Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 3948:883dd49d5e74

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Adapted PostgreSQL spatial schema. flys-backend/trunk@5585 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Mon, 24 Sep 2012 09:13:29 +0000
parents e41d03bf9807
children 82e931f88137
comparison
equal deleted inserted replaced
3947:371e38af6f79 3948:883dd49d5e74
3 -- Geodaesie/Flussachse+km/achse 3 -- Geodaesie/Flussachse+km/achse
4 CREATE SEQUENCE RIVER_AXES_ID_SEQ; 4 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
5 CREATE TABLE river_axes ( 5 CREATE TABLE river_axes (
6 id int PRIMARY KEY NOT NULL, 6 id int PRIMARY KEY NOT NULL,
7 river_id int REFERENCES rivers(id), 7 river_id int REFERENCES rivers(id),
8 kind int NOT NULL DEFAULT 0 8 kind int NOT NULL DEFAULT 0,
9 name VARCHAR(64),
10 path VARCHAR(256)
9 ); 11 );
10 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2); 12 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2);
11 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ'); 13 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');
12 14
13 15
15 -- Geodaesie/Flussachse+km/km.shp 17 -- Geodaesie/Flussachse+km/km.shp
16 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; 18 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
17 CREATE TABLE river_axes_km ( 19 CREATE TABLE river_axes_km (
18 id int PRIMARY KEY NOT NULL, 20 id int PRIMARY KEY NOT NULL,
19 river_id int REFERENCES rivers(id), 21 river_id int REFERENCES rivers(id),
20 km NUMERIC NOT NULL 22 km NUMERIC NOT NULL,
23 name VARCHAR(64),
24 path VARCHAR(256)
21 ); 25 );
22 SELECT AddGeometryColumn('river_axes_km', 'geom', 31466, 'POINT', 2); 26 SELECT AddGeometryColumn('river_axes_km', 'geom', 31466, 'POINT', 2);
23 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ'); 27 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ');
24 28
25 29
27 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; 31 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
28 CREATE TABLE cross_section_tracks ( 32 CREATE TABLE cross_section_tracks (
29 id int PRIMARY KEY NOT NULL, 33 id int PRIMARY KEY NOT NULL,
30 river_id int REFERENCES rivers(id), 34 river_id int REFERENCES rivers(id),
31 km NUMERIC NOT NULL, 35 km NUMERIC NOT NULL,
32 z NUMERIC NOT NULL DEFAULT 0 36 z NUMERIC NOT NULL DEFAULT 0,
37 name VARCHAR(64),
38 path VARCHAR(256)
33 ); 39 );
34 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2); 40 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2);
35 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ'); 41 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
36 42
37 43
39 CREATE SEQUENCE LINES_ID_SEQ; 45 CREATE SEQUENCE LINES_ID_SEQ;
40 CREATE TABLE lines ( 46 CREATE TABLE lines (
41 id int PRIMARY KEY NOT NULL, 47 id int PRIMARY KEY NOT NULL,
42 river_id int REFERENCES rivers(id), 48 river_id int REFERENCES rivers(id),
43 kind VARCHAR(16) NOT NULL, 49 kind VARCHAR(16) NOT NULL,
44 z NUMERIC DEFAULT 0 50 z NUMERIC DEFAULT 0,
51 name VARCHAR(64),
52 path VARCHAR(256)
45 ); 53 );
46 SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4); 54 SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4);
47 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ'); 55 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
48 -- 'kind': 56 -- 'kind':
49 -- 0: ROHR1 57 -- 0: ROHR1
53 -- Geodaesie/Bauwerke/Wehre.shp 61 -- Geodaesie/Bauwerke/Wehre.shp
54 CREATE SEQUENCE BUILDINGS_ID_SEQ; 62 CREATE SEQUENCE BUILDINGS_ID_SEQ;
55 CREATE TABLE buildings ( 63 CREATE TABLE buildings (
56 id int PRIMARY KEY NOT NULL, 64 id int PRIMARY KEY NOT NULL,
57 river_id int REFERENCES rivers(id), 65 river_id int REFERENCES rivers(id),
58 name VARCHAR(256) 66 name VARCHAR(256),
67 path VARCHAR(256)
59 ); 68 );
60 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2); 69 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2);
61 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ'); 70 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
62 71
63 72
67 id int PRIMARY KEY NOT NULL, 76 id int PRIMARY KEY NOT NULL,
68 river_id int REFERENCES rivers(id), 77 river_id int REFERENCES rivers(id),
69 x int, 78 x int,
70 y int, 79 y int,
71 km NUMERIC NOT NULL, 80 km NUMERIC NOT NULL,
72 HPGP VARCHAR(2) 81 HPGP VARCHAR(2),
82 name VARCHAR(64),
83 path VARCHAR(256)
73 ); 84 );
74 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2); 85 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2);
75 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ'); 86 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
76 87
77 88
78 -- Hydrologie/Hydr. Grenzen/talaue.shp 89 -- Hydrologie/Hydr. Grenzen/talaue.shp
79 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; 90 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
80 CREATE TABLE floodplain ( 91 CREATE TABLE floodplain (
81 id int PRIMARY KEY NOT NULL, 92 id int PRIMARY KEY NOT NULL,
82 river_id int REFERENCES rivers(id) 93 river_id int REFERENCES rivers(id),
94 name VARCHAR(64),
95 path VARCHAR(256)
83 ); 96 );
84 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'POLYGON', 2); 97 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'POLYGON', 2);
85 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ'); 98 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
86 99
87 100
103 CREATE SEQUENCE CATCHMENT_ID_SEQ; 116 CREATE SEQUENCE CATCHMENT_ID_SEQ;
104 CREATE TABLE catchment ( 117 CREATE TABLE catchment (
105 id int PRIMARY KEY NOT NULL, 118 id int PRIMARY KEY NOT NULL,
106 river_id int REFERENCES rivers(id), 119 river_id int REFERENCES rivers(id),
107 area NUMERIC, 120 area NUMERIC,
108 name VARCHAR(256) 121 name VARCHAR(256),
122 path VARCHAR(256)
109 ); 123 );
110 SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2); 124 SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2);
111 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ'); 125 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
112 126
113 127
115 CREATE SEQUENCE HWS_ID_SEQ; 129 CREATE SEQUENCE HWS_ID_SEQ;
116 CREATE TABLE hws ( 130 CREATE TABLE hws (
117 id int PRIMARY KEY NOT NULL, 131 id int PRIMARY KEY NOT NULL,
118 river_id int REFERENCES rivers(id), 132 river_id int REFERENCES rivers(id),
119 hws_facility VARCHAR(256), 133 hws_facility VARCHAR(256),
120 type VARCHAR(256) 134 type VARCHAR(256),
135 name VARCHAR(64),
136 path VARCHAR(256)
121 ); 137 );
122 SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2); 138 SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2);
123 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); 139 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
124 140
125 141
131 -- 111 = Berechnung->Aktuell->BfG 147 -- 111 = Berechnung->Aktuell->BfG
132 -- 112 = Berechnung->Aktuell->Land 148 -- 112 = Berechnung->Aktuell->Land
133 -- 121 = Berechnung->Potenziell->BfG 149 -- 121 = Berechnung->Potenziell->BfG
134 -- 122 = Berechnung->Potenziell->Land 150 -- 122 = Berechnung->Potenziell->Land
135 -- 151 --
136 CREATE SEQUENCE FLOODMAPS_SEQ; 152 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
137 CREATE FUNCTION floodmaps_id_func() RETURNS trigger AS $floodmaps_id_func$
138 BEGIN
139 NEW.id := nextval('floodmaps_seq');
140 RETURN NEW;
141 END;
142 $floodmaps_id_func$ LANGUAGE plpgsql;
143
144 CREATE TABLE floodmaps ( 153 CREATE TABLE floodmaps (
145 id int PRIMARY KEY NOT NULL, 154 id int PRIMARY KEY NOT NULL,
146 river_id int REFERENCES rivers(id), 155 river_id int REFERENCES rivers(id),
147 name varchar(64) NOT NULL, 156 name varchar(64) NOT NULL,
148 kind int NOT NULL, 157 kind int NOT NULL,
149 diff real, 158 diff real,
150 count int, 159 count int,
151 area real, 160 area real,
152 perimeter real 161 perimeter real,
162 path VARCHAR(256)
153 ); 163 );
154 SELECT AddGeometryColumn('floodmaps', 'geom', 31466, 'MULTIPOLYGON', 2); 164 SELECT AddGeometryColumn('floodmaps', 'geom', 31466, 'MULTIPOLYGON', 2);
155 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom; 165 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
156 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text); 166 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
157 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_SEQ'); 167 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
158 168
159 CREATE TRIGGER floodmaps_id_trigger BEFORE INSERT OR UPDATE ON floodmaps 169
160 FOR EACH ROW EXECUTE PROCEDURE floodmaps_id_func(); 170 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
161 END; 171 CREATE TABLE hydr_boundaries (
172 id int PRIMARY KEY NOT NULL,
173 river_id int REFERENCES rivers(id),
174 name VARCHAR(255),
175 kind int,
176 path VARCHAR(256)
177 );
178 SELECT AddGeometryColumn('hydr_boundaries','geom',31466,'LINESTRING',4);
179 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ');
180
181
182 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
183 CREATE TABLE hydr_boundaries_poly (
184 id int PRIMARY KEY NOT NULL,
185 river_id int REFERENCES rivers(id),
186 name VARCHAR(255),
187 kind int,
188 path VARCHAR(256)
189 );
190 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31466,'POLYGON',4);
191 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');
192
193
194 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
195 CREATE TABLE gauge_location (
196 id int PRIMARY KEY NOT NULL,
197 river_id int REFERENCES rivers(id),
198 name VARCHAR(255),
199 path VARCHAR(256)
200 );
201 SELECT AddGeometryColumn('gauge_location','geom',31466,'POINT',2);
202 ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ');
203
204 COMMIT;
http://dive4elements.wald.intevation.org