Mercurial > dive4elements > river
annotate flys-backend/doc/schema/postgresql-spatial.sql @ 4678:83838f87e98f
Whitespace-cosmetics.
author | Felix Wolfsteller <felix.wolfsteller@intevation.de> |
---|---|
date | Fri, 14 Dec 2012 10:41:24 +0100 |
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; |