Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql-spatial.sql @ 4259:5cc9453456a7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
First complete but untested version of the 'Auslagerung extremer Wasserspiegellagen' calculation.
author Sascha L. Teichmann <teichmann@intevation.de>
date Thu, 25 Oct 2012 17:25:37 +0200
parents f5912365619c
children 28f992c0f937
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
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
135
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
136 --Hydrologie/HW-Schutzanlagen/hws.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
137 CREATE SEQUENCE HWS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
138 CREATE TABLE hws (
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
139 id int PRIMARY KEY NOT NULL,
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
140 river_id int REFERENCES rivers(id),
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
141 hws_facility VARCHAR(256),
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
142 type VARCHAR(256),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
143 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
144 path VARCHAR(256)
2339
b28ab244a77d Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1243
diff changeset
145 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
146 SELECT AddGeometryColumn('hws','geom',31467,'LINESTRING',2);
2376
918877ee70e7 backend: Fixed spatial table hws.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2365
diff changeset
147 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
1241
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
148
f68a0504dfb6 Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents: 1240
diff changeset
149
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
150 --
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
151 --Hydrologie/UeSG
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
152 --
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
153 -- 'kind' can be one of:
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
154 -- 200 = Messung
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
155 -- 111 = Berechnung->Aktuell->BfG
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
156 -- 112 = Berechnung->Aktuell->Land
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
157 -- 121 = Berechnung->Potenziell->BfG
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
158 -- 122 = Berechnung->Potenziell->Land
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
159 --
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
160 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
161 CREATE TABLE floodmaps (
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
162 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
163 river_id int REFERENCES rivers(id),
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
164 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
165 kind int NOT NULL,
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
166 diff real,
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
167 count int,
2797
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2796
diff changeset
168 area real,
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
169 perimeter real,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
170 path VARCHAR(256)
2796
f3b4e32b5db6 Improved the db schema for storing existing floodmaps.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2376
diff changeset
171 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
172 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
173 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
174 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
175 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
176
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
177
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
178 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
179 CREATE TABLE hydr_boundaries (
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
180 id int PRIMARY KEY NOT NULL,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
181 river_id int REFERENCES rivers(id),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
182 name VARCHAR(255),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
183 kind int,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
184 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
185 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
186 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3);
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
187 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
188
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
189
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
190 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
191 CREATE TABLE hydr_boundaries_poly (
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
192 id int PRIMARY KEY NOT NULL,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
193 river_id int REFERENCES rivers(id),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
194 name VARCHAR(255),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
195 kind int,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
196 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
197 );
3950
82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3948
diff changeset
198 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
199 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
200
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
201
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
202 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
203 CREATE TABLE gauge_location (
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
204 id int PRIMARY KEY NOT NULL,
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
205 river_id int REFERENCES rivers(id),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
206 name VARCHAR(255),
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
207 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
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('gauge_location','geom',31467,'POINT',2);
3948
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
210 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
211
883dd49d5e74 Adapted PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2797
diff changeset
212 COMMIT;
http://dive4elements.wald.intevation.org