Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql-spatial.sql @ 5037:2837b11cdb13 dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SCHEMA Change: Make hws_lines a 3d geom
author Andre Heinecke <aheinecke@intevation.de>
date Wed, 20 Feb 2013 10:43:00 +0100
parents a5f438895a38
children e4cc9aebfcf1
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
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
3 -- Geodaesie/Flussachse+km/achse
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
4 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
5 CREATE TABLE river_axes (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
6 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
7 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
8 kind int NOT NULL DEFAULT 0,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
9 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
10 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
11 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
12 SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'LINESTRING', 2);
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
13 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
14
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
15
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
16 -- TODO: TestMe.
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
17 -- Geodaesie/Flussachse+km/km.shp
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
18 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
19 CREATE TABLE river_axes_km (
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
20 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
21 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
22 km FLOAT8 NOT NULL,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
23 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
24 path VARCHAR(256)
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
25 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
26 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2);
2340
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
27 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
28
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
29
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
30 --Geodaesie/Querprofile/QP-Spuren/qps.shp
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
31 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
32 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
33 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
34 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
35 km FLOAT8 NOT NULL,
f5c55d7ea07e SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents: 4930
diff changeset
36 z FLOAT8 NOT NULL DEFAULT 0,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
37 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
38 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
39 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
40 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
41 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
42
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
43
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
44 -- Geodaesie/Bauwerke/Wehre.shp
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
45 CREATE SEQUENCE BUILDINGS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
46 CREATE TABLE buildings (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
47 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
48 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
49 name VARCHAR(256),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
50 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
51 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
52 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
53 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
54
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
55
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
56 -- Geodaesie/Festpunkte/Festpunkte.shp
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
57 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
58 CREATE TABLE fixpoints (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
59 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
60 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
61 x FLOAT8,
f5c55d7ea07e SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents: 4930
diff changeset
62 y FLOAT8,
f5c55d7ea07e SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents: 4930
diff changeset
63 km FLOAT8 NOT NULL,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
64 HPGP VARCHAR(2),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
65 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
66 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
67 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
68 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
69 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
70
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
71
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
72 -- Hydrologie/Hydr. Grenzen/talaue.shp
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
73 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
74 CREATE TABLE floodplain (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
75 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
76 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
77 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
78 path VARCHAR(256)
1237
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
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('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
81 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
82
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
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/Hoehenmodelle/*
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
85 CREATE SEQUENCE DEM_ID_SEQ;
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
86 CREATE TABLE dem (
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,
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
89 -- XXX Should we use the ranges table instead?
3951
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3950
diff changeset
90 name VARCHAR(64),
4931
f5c55d7ea07e SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents: 4930
diff changeset
91 lower FLOAT8,
f5c55d7ea07e SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents: 4930
diff changeset
92 upper FLOAT8,
3961
f5912365619c Adapted the spatial schema for DEMs and the DgmSqlConverter.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3951
diff changeset
93 year_from VARCHAR(32) NOT NULL,
f5912365619c Adapted the spatial schema for DEMs and the DgmSqlConverter.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3951
diff changeset
94 year_to VARCHAR(32) NOT NULL,
f5912365619c Adapted the spatial schema for DEMs and the DgmSqlConverter.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3951
diff changeset
95 projection VARCHAR(32) NOT NULL,
4992
ecc6fd57b630 SCHEMA CHANGE: added column dem.srid
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4991
diff changeset
96 srid int NOT NULL,
3951
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3950
diff changeset
97 elevation_state VARCHAR(32),
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3950
diff changeset
98 format VARCHAR(32),
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3950
diff changeset
99 border_break BOOLEAN NOT NULL DEFAULT FALSE,
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3950
diff changeset
100 resolution VARCHAR(16),
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3950
diff changeset
101 description VARCHAR(256),
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3950
diff changeset
102 path VARCHAR(256)
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
103 );
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
104 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
105
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
106
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
107 -- Static lookup tables for Hochwasserschutzanlagen
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
108 CREATE TABLE hws_kinds (
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
109 id int PRIMARY KEY NOT NULL,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
110 kind VARCHAR(64) NOT NULL
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
111 );
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
112 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
113 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
114 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
115
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
116 CREATE TABLE fed_states (
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
117 id int PRIMARY KEY NOT NULL,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
118 name VARCHAR(23) NOT NULL
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
119 );
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
120 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
121 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
122 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
123 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
124 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
125 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
126 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
127 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
128 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
129 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
130 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
131 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
132 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
133 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
134 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
135 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
136
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
137 --Hydrologie/HW-Schutzanlagen/*Linien.shp
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
138 CREATE SEQUENCE HWS_LINES_ID_SEQ;
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
139 CREATE TABLE hws_lines (
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
140 id int PRIMARY KEY NOT NULL,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
141 ogr_fid int,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
142 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
143 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
144 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
145 name VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
146 path VARCHAR(256),
5010
ee52faa6b7ac Corrected typo in field name
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4999
diff changeset
147 official INT DEFAULT 0,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
148 agency VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
149 range VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
150 shore_side INT DEFAULT 0,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
151 source VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
152 status_date TIMESTAMP,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
153 description VARCHAR(256)
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
154 );
5037
2837b11cdb13 SCHEMA Change: Make hws_lines a 3d geom
Andre Heinecke <aheinecke@intevation.de>
parents: 5028
diff changeset
155 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'LINESTRING', 3);
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
156 -- 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
157
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
158 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
159
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
160 --Hydrologie/HW-Schutzanlagen/*Punkte.shp
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
161 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
162 CREATE TABLE hws_points (
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
163 id int PRIMARY KEY NOT NULL,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
164 ogr_fid int,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
165 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
166 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
167 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
168 name VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
169 path VARCHAR,
5010
ee52faa6b7ac Corrected typo in field name
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4999
diff changeset
170 official INT DEFAULT 0,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
171 agency VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
172 range VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
173 shore_side INT DEFAULT 0,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
174 source VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
175 status_date VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
176 description VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
177 freeboard FLOAT8,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
178 dike_km FLOAT8,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
179 z FLOAT8,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
180 z_target FLOAT8,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
181 rated_level FLOAT8
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
182 );
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
183 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
184
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
185 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
186
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
187 --
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
188 --Hydrologie/UeSG
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
189 --
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
190 -- 'kind' can be one of:
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
191 -- 200 = Messung
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
192 -- 111 = Berechnung->Aktuell->BfG
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
193 -- 112 = Berechnung->Aktuell->Land
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
194 -- 121 = Berechnung->Potenziell->BfG
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
195 -- 122 = Berechnung->Potenziell->Land
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
196 --
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
197 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
198 CREATE TABLE floodmaps (
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
199 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
200 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
201 name varchar(64) NOT NULL,
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
202 kind int NOT NULL,
4931
f5c55d7ea07e SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents: 4930
diff changeset
203 diff FLOAT8,
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
204 count int,
4931
f5c55d7ea07e SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents: 4930
diff changeset
205 area FLOAT8,
f5c55d7ea07e SCHEMA Change (psql only) use FLOAT8 for real numbers
Andre Heinecke <aheinecke@intevation.de>
parents: 4930
diff changeset
206 perimeter FLOAT8,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
207 path VARCHAR(256)
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
208 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
209 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
210 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
211 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
212 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
213
4999
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
214 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
215 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
216 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
217 );
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
218 INSERT INTO sectie_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
219 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
220 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
221 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
222
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
223 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
224 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
225 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
226 );
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
227 INSERT INTO sobek_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
228 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
229 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
230
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
231 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
232 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
233 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
234 );
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
235 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
236 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
237 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
238 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
239
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
240 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
241 CREATE TABLE hydr_boundaries (
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
242 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
243 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
244 name VARCHAR(255),
4999
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
245 kind int REFERENCES boundary_kinds(id),
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
246 sectie int REFERENCES sectie_kinds(id),
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
247 sobek int REFERENCES sobek_kinds(id),
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
248 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
249 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
250 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3);
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
251 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
252
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
253
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
254 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
255 CREATE TABLE hydr_boundaries_poly (
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
256 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
257 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
258 name VARCHAR(255),
4999
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
259 kind int REFERENCES boundary_kinds(id),
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
260 sectie int REFERENCES sectie_kinds(id),
929ec3ed7dc2 SCHEMA_CHANGE: Add lookup tables for sectie / sobek and boundary_kind
Andre Heinecke <aheinecke@intevation.de>
parents: 4992
diff changeset
261 sobek int REFERENCES sobek_kinds(id),
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
262 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
263 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
264 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'POLYGON',3);
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
265 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
266
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
267
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
268 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
269 CREATE TABLE gauge_location (
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
270 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
271 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
272 name VARCHAR(255),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
273 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
274 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
275 SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2);
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
276 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
277
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
278 COMMIT;
http://dive4elements.wald.intevation.org