Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql-spatial.sql @ 5531:ac48ab9e1715

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Datacage: Added connection to SedDB. Use <dc:context connection="seddb"> to use it.
author Sascha L. Teichmann <teichmann@intevation.de>
date Wed, 03 Apr 2013 12:54:34 +0200
parents 4c4fed23693f
children 0aaed46a88be
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,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
31 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
32 path VARCHAR(256)
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
33 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
34 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2);
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
35 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
36
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 --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
39 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
40 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
41 name VARCHAR(64)
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
42 );
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
43 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
44 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
45
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
46 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
47 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
48 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
49 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
50 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
51 km FLOAT8 NOT NULL,
f5c55d7ea07e SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents: 4930
diff changeset
52 z FLOAT8 NOT NULL DEFAULT 0,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
53 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
54 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
55 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
56 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
57 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
58
5385
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5363
diff changeset
59 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
60 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
61 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
62 );
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5363
diff changeset
63 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
64 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
65 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
66 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
67
5385
2da74705c29d Scheme change: Building table is extended and got a lookup table for kind
Andre Heinecke <aheinecke@intevation.de>
parents: 5363
diff changeset
68 -- Geodaesie/Bauwerke
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
69 CREATE SEQUENCE BUILDINGS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
70 CREATE TABLE buildings (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
71 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
72 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
73 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
74 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
75 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
76 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
77 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
78 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
79 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
80 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
81
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
82
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
83 -- Geodaesie/Festpunkte/Festpunkte.shp
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
84 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
85 CREATE TABLE fixpoints (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
86 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
87 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
88 x FLOAT8,
f5c55d7ea07e SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents: 4930
diff changeset
89 y FLOAT8,
f5c55d7ea07e SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents: 4930
diff changeset
90 km FLOAT8 NOT NULL,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
91 HPGP VARCHAR(2),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
92 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
93 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
94 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
95 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
96 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
97
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
98
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
99 -- 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
100 CREATE TABLE floodplain_kinds(
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
101 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
102 name VARCHAR(64)
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
103 );
b46df0609276 add lookup tables for floodplain and cross section tracks
Tom Gottfried <tom@intevation.de>
parents: 5210
diff changeset
104 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
105 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
106
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
107 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
108 CREATE TABLE floodplain (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
109 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
110 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
111 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
112 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
113 path VARCHAR(256)
1237
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
114 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
115 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
116 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
117
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
118
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
119 -- Geodaesie/Hoehenmodelle/*
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
120 CREATE SEQUENCE DEM_ID_SEQ;
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
121 CREATE TABLE dem (
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
122 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
123 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
124 name VARCHAR(64),
5289
f6478773a5bb postgresql-spatial.sql: Fixed table definition.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 5276
diff changeset
125 range_id INT REFERENCES ranges(id),
5291
66d9389c80cb added tables for jetties in spatial schema
Tom Gottfried <tom@intevation.de>
parents: 5276
diff changeset
126 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
127 projection VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5182
diff changeset
128 srid int NOT NULL,
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5182
diff changeset
129 elevation_state VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5182
diff changeset
130 format VARCHAR(32),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5182
diff changeset
131 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
132 resolution VARCHAR(16),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5182
diff changeset
133 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
134 path VARCHAR(256) NOT NULL
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
135 );
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
136 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
137
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
138
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
139 -- Static lookup tables for Hochwasserschutzanlagen
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
140 CREATE TABLE hws_kinds (
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
141 id int PRIMARY KEY NOT NULL,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
142 kind VARCHAR(64) NOT NULL
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
143 );
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
144 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
145 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
146 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
147
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
148 CREATE TABLE fed_states (
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
149 id int PRIMARY KEY NOT NULL,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
150 name VARCHAR(23) NOT NULL
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
151 );
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
152 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
153 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
154 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
155 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
156 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
157 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
158 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
159 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
160 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
161 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
162 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
163 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
164 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
165 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
166 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
167 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
168
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
169 --Hydrologie/HW-Schutzanlagen/*Linien.shp
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
170 CREATE SEQUENCE HWS_LINES_ID_SEQ;
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
171 CREATE TABLE hws_lines (
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
172 id int PRIMARY KEY NOT NULL,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
173 ogr_fid int,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
174 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
175 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
176 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
177 name VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
178 path VARCHAR(256),
5010
ee52faa6b7ac Corrected typo in field name
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4999
diff changeset
179 official INT DEFAULT 0,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
180 agency VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
181 range VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
182 shore_side INT DEFAULT 0,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
183 source VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
184 status_date TIMESTAMP,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
185 description VARCHAR(256)
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
186 );
5137
04eb62eae722 River Axes and HWS_lines are now multiline
Andre Heinecke <aheinecke@intevation.de>
parents: 5121
diff changeset
187 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
188 -- 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
189
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
190 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
191
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
192 --Hydrologie/HW-Schutzanlagen/*Punkte.shp
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
193 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
194 CREATE TABLE hws_points (
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
195 id int PRIMARY KEY NOT NULL,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
196 ogr_fid int,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
197 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
198 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
199 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
200 name VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
201 path VARCHAR,
5010
ee52faa6b7ac Corrected typo in field name
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4999
diff changeset
202 official INT DEFAULT 0,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
203 agency VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
204 range VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
205 shore_side INT DEFAULT 0,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
206 source VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
207 status_date VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
208 description VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
209 freeboard FLOAT8,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
210 dike_km FLOAT8,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
211 z FLOAT8,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
212 z_target FLOAT8,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
213 rated_level FLOAT8
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
214 );
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
215 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
216
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
217 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
218
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
219 --
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
220 --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
221 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
222 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
223 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
224 );
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 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
226 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
227 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
228 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
229 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
230
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
231 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
232 CREATE TABLE floodmaps (
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
233 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
234 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
235 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
236 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
237 diff FLOAT8,
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
238 count int,
4931
f5c55d7ea07e SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents: 4930
diff changeset
239 area FLOAT8,
f5c55d7ea07e SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents: 4930
diff changeset
240 perimeter FLOAT8,
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
241 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
242 source varchar(64)
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
243 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
244 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
245 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
246 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
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 );
5514
4c4fed23693f Cosmetic scheme change. Qualify the unknown name of sectie and sobek
Andre Heinecke <aheinecke@intevation.de>
parents: 5513
diff changeset
253 INSERT INTO sectie_kinds (id, name) VALUES (0, 'SECTIE Unbekannt');
4999
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
254 INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch');
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
255 INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank');
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
256 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich');
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 );
5514
4c4fed23693f Cosmetic scheme change. Qualify the unknown name of sectie and sobek
Andre Heinecke <aheinecke@intevation.de>
parents: 5513
diff changeset
262 INSERT INTO sobek_kinds (id, name) VALUES (0, 'SOBEK Unbekannt');
4999
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
263 INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend');
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
264 INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd');
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,
ef3517905e6e Add not null and default for hydr_boundary classifiers
Andre Heinecke <aheinecke@intevation.de>
parents: 5495
diff changeset
281 sectie int REFERENCES sectie_kinds(id) NOT NULL DEFAULT 0,
ef3517905e6e Add not null and default for hydr_boundary classifiers
Andre Heinecke <aheinecke@intevation.de>
parents: 5495
diff changeset
282 sobek int REFERENCES sobek_kinds(id) NOT NULL DEFAULT 0,
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,
ef3517905e6e Add not null and default for hydr_boundary classifiers
Andre Heinecke <aheinecke@intevation.de>
parents: 5495
diff changeset
295 sectie int REFERENCES sectie_kinds(id) NOT NULL DEFAULT 0,
ef3517905e6e Add not null and default for hydr_boundary classifiers
Andre Heinecke <aheinecke@intevation.de>
parents: 5495
diff changeset
296 sobek int REFERENCES sobek_kinds(id) NOT NULL DEFAULT 0,
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;
http://dive4elements.wald.intevation.org