Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql-spatial.sql @ 4930:28f992c0f937 dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen This adds the first draft of Hochwasserschutzanlagen tables hws_points and hws_lines which replace the old hws table and also lookup tables fed_states and hws_kinds.
author Andre Heinecke <aheinecke@intevation.de>
date Mon, 28 Jan 2013 17:42:18 +0100
parents f5912365619c
children f5c55d7ea07e
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,
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
7 river_id int REFERENCES rivers(id),
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,
c4f090a00178 Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2339
diff changeset
21 river_id int REFERENCES rivers(id),
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
22 km NUMERIC NOT NULL,
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,
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
34 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
35 km NUMERIC NOT NULL,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
36 z NUMERIC NOT NULL DEFAULT 0,
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/Linien/rohre-und-spreen
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
45 CREATE SEQUENCE LINES_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
46 CREATE TABLE lines (
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,
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
48 river_id int REFERENCES rivers(id),
2365
60532f75396b Adapted 'lines' relation and made lines queryable for a given river.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2363
diff changeset
49 kind VARCHAR(16) NOT NULL,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
50 z NUMERIC DEFAULT 0,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
51 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
52 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
53 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
54 SELECT AddGeometryColumn('lines', 'geom', 31467, 'LINESTRING', 3);
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
55 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
56 -- 'kind':
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
57 -- 0: ROHR1
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
58 -- 1: DAMM
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
59
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
60
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
61 -- Geodaesie/Bauwerke/Wehre.shp
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
62 CREATE SEQUENCE BUILDINGS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
63 CREATE TABLE buildings (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
64 id int PRIMARY KEY NOT NULL,
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
65 river_id int REFERENCES rivers(id),
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
66 name VARCHAR(256),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
67 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
68 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
69 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
70 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
71
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
72
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
73 -- Geodaesie/Festpunkte/Festpunkte.shp
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
74 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
75 CREATE TABLE fixpoints (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
76 id int PRIMARY KEY NOT NULL,
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
77 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
78 x int,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
79 y int,
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
80 km NUMERIC NOT NULL,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
81 HPGP VARCHAR(2),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
82 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
83 path VARCHAR(256)
1230
e2deda3b77a7 Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
84 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
85 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
86 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
87
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
88
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
89 -- 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
90 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
91 CREATE TABLE floodplain (
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
92 id int PRIMARY KEY NOT NULL,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
93 river_id int REFERENCES rivers(id),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
94 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
95 path VARCHAR(256)
1237
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
96 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
97 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
98 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
99
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1236
diff changeset
100
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
101 -- Geodaesie/Hoehenmodelle/*
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
102 CREATE SEQUENCE DEM_ID_SEQ;
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
103 CREATE TABLE dem (
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
104 id int PRIMARY KEY NOT NULL,
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
105 river_id int REFERENCES rivers(id),
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
106 -- 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
107 name VARCHAR(64),
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
108 lower NUMERIC,
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
109 upper NUMERIC,
3961
f5912365619c Adapted the spatial schema for DEMs and the DgmSqlConverter.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3951
diff changeset
110 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
111 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
112 projection VARCHAR(32) 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
113 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
114 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
115 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
116 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
117 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
118 path VARCHAR(256)
1236
324cb486db9f Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1230
diff changeset
119 );
1240
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 1239
diff changeset
120 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
121
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
122
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
123 -- Hydrologie/Einzugsgebiete/EZG.shp
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
124 CREATE SEQUENCE CATCHMENT_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
125 CREATE TABLE catchment (
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
126 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
127 river_id int REFERENCES rivers(id),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
128 area NUMERIC,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
129 name VARCHAR(256),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
130 path VARCHAR(256)
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
131 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
132 SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2);
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
133 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
134
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
135 -- Static lookup tables for Hochwasserschutzanlagen
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
136 CREATE TABLE hws_kinds (
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
137 id int PRIMARY KEY NOT NULL,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
138 kind VARCHAR(64) NOT NULL
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
139 );
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
140 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
141 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
142 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
143
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
144 CREATE TABLE fed_states (
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
145 id int PRIMARY KEY NOT NULL,
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
146 name VARCHAR(23) NOT NULL
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
147 );
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
148 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
149 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
150 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
151 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
152 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
153 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
154 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
155 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
156 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
157 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
158 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
159 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
160 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
161 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
162 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
163 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
164
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
165 --Hydrologie/HW-Schutzanlagen/*Linien.shp
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
166 CREATE SEQUENCE HWS_LINES_ID_SEQ;
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
167 CREATE TABLE hws_lines (
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
168 id int PRIMARY KEY NOT NULL,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
169 ogr_fid int,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
170 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
171 fed_state_id int REFERENCES fed_states(id),
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
172 river_id int REFERENCES rivers(id),
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
173 name VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
174 path VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
175 offical INT DEFAULT 0,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
176 agency VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
177 range VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
178 shore_side INT DEFAULT 0,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
179 source VARCHAR(256),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
180 status_date TIMESTAMP,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
181 description VARCHAR(256)
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
182 );
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
183 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'LINESTRING', 2);
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
184 SELECT AddGeometryColumn('hws_lines', 'geom_target', 31467, 'LINESTRING', 2); -- ?
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
185 SELECT AddGeometryColumn('hws_lines', 'geom_rated_level', 31467, 'LINESTRING', 2); -- ?
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
186 -- 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
187
4930
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
188 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
189
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
190 --Hydrologie/HW-Schutzanlagen/*Punkte.shp
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
191 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
192 CREATE TABLE hws_points (
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
193 id int PRIMARY KEY NOT NULL,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
194 ogr_fid int,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
195 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
196 fed_state_id int REFERENCES fed_states(id),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
197 river_id int REFERENCES rivers(id),
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
198 name VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
199 path VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
200 offical INT DEFAULT 0,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
201 agency VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
202 range VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
203 shore_side INT DEFAULT 0,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
204 source VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
205 status_date VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
206 description VARCHAR,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
207 freeboard FLOAT8,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
208 dike_km FLOAT8,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
209 z FLOAT8,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
210 z_target FLOAT8,
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
211 rated_level FLOAT8
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
212 );
28f992c0f937 SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
Andre Heinecke <aheinecke@intevation.de>
parents: 3961
diff changeset
213 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);
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 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
216
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
217 --
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
218 --Hydrologie/UeSG
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
219 --
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
220 -- 'kind' can be one of:
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
221 -- 200 = Messung
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
222 -- 111 = Berechnung->Aktuell->BfG
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
223 -- 112 = Berechnung->Aktuell->Land
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
224 -- 121 = Berechnung->Potenziell->BfG
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
225 -- 122 = Berechnung->Potenziell->Land
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
226 --
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
227 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
228 CREATE TABLE floodmaps (
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
229 id int PRIMARY KEY NOT NULL,
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
230 river_id int REFERENCES rivers(id),
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
231 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
232 kind int NOT NULL,
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
233 diff real,
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
234 count int,
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
235 area real,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
236 perimeter real,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
237 path VARCHAR(256)
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
238 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
239 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
240 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
241 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
242 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
243
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
244
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
245 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
246 CREATE TABLE hydr_boundaries (
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
247 id int PRIMARY KEY NOT NULL,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
248 river_id int REFERENCES rivers(id),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
249 name VARCHAR(255),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
250 kind int,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
251 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
252 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
253 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3);
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
254 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
255
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
256
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
257 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
258 CREATE TABLE hydr_boundaries_poly (
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
259 id int PRIMARY KEY NOT NULL,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
260 river_id int REFERENCES rivers(id),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
261 name VARCHAR(255),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
262 kind int,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
263 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
264 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
265 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
266 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
267
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
268
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
269 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
270 CREATE TABLE gauge_location (
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
271 id int PRIMARY KEY NOT NULL,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
272 river_id int REFERENCES rivers(id),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
273 name VARCHAR(255),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
274 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
275 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
276 SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2);
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
277 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
278
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
279 COMMIT;
http://dive4elements.wald.intevation.org