Mercurial > dive4elements > river
annotate flys-backend/doc/schema/postgresql-spatial.sql @ 2360:9df06b88c079
Added model class for relation 'river_axes_km'.
flys-backend/trunk@3133 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
---|---|
date | Tue, 01 Nov 2011 15:30:38 +0000 |
parents | b8e01307c138 |
children | a8f408fdf58c |
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), |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
8 kind int NOT NULL DEFAULT 0 |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
9 ); |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
10 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2); |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
11 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
|
12 |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
13 |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
14 -- TODO: TestMe. |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
15 -- Geodaesie/Flussachse+km/km.shp |
2340
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
16 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
17 CREATE TABLE river_axes_km ( |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
18 id int PRIMARY KEY NOT NULL, |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
19 river_id int REFERENCES rivers(id), |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
20 km NUMERIC NOT NULL |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
21 ); |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
22 SELECT AddGeometryColumn('river_axes_km', 'geom', 31466, 'POINT', 2); |
c4f090a00178
Added missing schemas.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2339
diff
changeset
|
23 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
|
24 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
25 |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
26 --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
|
27 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; |
1230
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
28 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
|
29 id int PRIMARY KEY NOT NULL, |
1230
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
30 river_id int REFERENCES rivers(id), |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
31 km NUMERIC NOT NULL, |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
32 z NUMERIC NOT NULL DEFAULT 0 |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
33 ); |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
34 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2); |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
35 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
|
36 |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
37 |
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
|
38 -- 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
|
39 CREATE SEQUENCE LINES_ID_SEQ; |
1230
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
40 CREATE TABLE lines ( |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
41 id int PRIMARY KEY NOT NULL, |
1230
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
42 river_id int REFERENCES rivers(id), |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
43 kind int NOT NULL DEFAULT 0, |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
44 z NUMERIC DEFAULT 0 |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
45 ); |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
46 SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4); |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
47 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
|
48 -- 'kind': |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
49 -- 0: ROHR1 |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
50 -- 1: DAMM |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
51 |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
52 |
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
|
53 -- Geodaesie/Bauwerke/Wehre.shp |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
54 CREATE SEQUENCE BUILDINGS_ID_SEQ; |
1230
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
55 CREATE TABLE buildings ( |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
56 id int PRIMARY KEY NOT NULL, |
1230
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
57 river_id int REFERENCES rivers(id), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
58 name VARCHAR(256) |
1230
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 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2); |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
61 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
|
62 |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
63 |
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
|
64 -- Geodaesie/Festpunkte/Festpunkte.shp |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
65 CREATE SEQUENCE FIXPOINTS_ID_SEQ; |
1230
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
66 CREATE TABLE fixpoints ( |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
67 id int PRIMARY KEY NOT NULL, |
1230
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
68 river_id int REFERENCES rivers(id), |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
69 x int, |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
70 y int, |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
71 km NUMERIC NOT NULL, |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
72 HPGP VARCHAR(2) |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
73 ); |
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
74 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2); |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
75 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
|
76 |
1236
324cb486db9f
Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1230
diff
changeset
|
77 |
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
|
78 -- 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
|
79 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
|
80 CREATE TABLE floodplain ( |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
81 id int PRIMARY KEY NOT NULL, |
1237
774dff3ee546
Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1236
diff
changeset
|
82 river_id int REFERENCES rivers(id) |
774dff3ee546
Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1236
diff
changeset
|
83 ); |
2356
b8e01307c138
Adjusted geometry type for floodplain and hws in AddGeometryColumn.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2340
diff
changeset
|
84 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'POLYGON', 2); |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
85 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
|
86 |
774dff3ee546
Added relation and hibernate classes for floodplains (german 'Talaue').
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1236
diff
changeset
|
87 |
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
|
88 -- Geodaesie/Hoehenmodelle/* |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
89 CREATE SEQUENCE DEM_ID_SEQ; |
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
90 CREATE TABLE dem ( |
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
91 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
|
92 river_id int REFERENCES rivers(id), |
324cb486db9f
Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1230
diff
changeset
|
93 -- XXX Should we use the ranges table instead? |
324cb486db9f
Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1230
diff
changeset
|
94 lower NUMERIC, |
324cb486db9f
Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1230
diff
changeset
|
95 upper NUMERIC, |
324cb486db9f
Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1230
diff
changeset
|
96 path VARCHAR(256), |
324cb486db9f
Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1230
diff
changeset
|
97 UNIQUE (river_id, lower, upper) |
324cb486db9f
Added relation and hibernate classes for DGMs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1230
diff
changeset
|
98 ); |
1240
2ef8b67bd68a
Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
1239
diff
changeset
|
99 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
|
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 |
1241
f68a0504dfb6
Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1240
diff
changeset
|
102 -- Hydrologie/Einzugsgebiete/EZG.shp |
f68a0504dfb6
Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1240
diff
changeset
|
103 CREATE SEQUENCE CATCHMENT_ID_SEQ; |
f68a0504dfb6
Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1240
diff
changeset
|
104 CREATE TABLE catchment ( |
f68a0504dfb6
Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1240
diff
changeset
|
105 id int PRIMARY KEY NOT NULL, |
f68a0504dfb6
Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1240
diff
changeset
|
106 river_id int REFERENCES rivers(id), |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
107 area NUMERIC, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
108 name VARCHAR(256) |
1241
f68a0504dfb6
Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1240
diff
changeset
|
109 ); |
f68a0504dfb6
Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1240
diff
changeset
|
110 SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2); |
f68a0504dfb6
Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1240
diff
changeset
|
111 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
|
112 |
f68a0504dfb6
Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1240
diff
changeset
|
113 |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
114 --Hydrologie/HW-Schutzanlagen/hws.shp |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
115 CREATE SEQUENCE HWS_ID_SEQ; |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
116 CREATE TABLE hws ( |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
117 id int PRIMARY KEY NOT NULL, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
118 oid int, |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
119 river_id int REFERENCES rivers(id), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
120 hws_facility VARCHAR(256), |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
121 typ VARCHAR(256) |
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
122 ); |
2356
b8e01307c138
Adjusted geometry type for floodplain and hws in AddGeometryColumn.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2340
diff
changeset
|
123 SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2); |
2339
b28ab244a77d
Harmonized oracle and postgresql spatial schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1243
diff
changeset
|
124 ALTER TABLE hw ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); |
1241
f68a0504dfb6
Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1240
diff
changeset
|
125 |
f68a0504dfb6
Postgresql spatial scheme extended.
Bjoern Schilberg <bjoern@intevation.de>
parents:
1240
diff
changeset
|
126 |
1230
e2deda3b77a7
Introduced support for FLYS spatial data.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
127 END; |