Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 3950:82e931f88137

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Adapted postgresql spatial schema and fixed a bug in the geo importer. flys-backend/trunk@5587 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Mon, 24 Sep 2012 10:02:36 +0000
parents 883dd49d5e74
children 89ada0b9083f
comparison
equal deleted inserted replaced
3949:145b87bcb10a 3950:82e931f88137
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), 9 name VARCHAR(64),
10 path VARCHAR(256) 10 path VARCHAR(256)
11 ); 11 );
12 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2); 12 SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'LINESTRING', 2);
13 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');
14 14
15 15
16 -- TODO: TestMe. 16 -- TODO: TestMe.
17 -- Geodaesie/Flussachse+km/km.shp 17 -- Geodaesie/Flussachse+km/km.shp
21 river_id int REFERENCES rivers(id), 21 river_id int REFERENCES rivers(id),
22 km NUMERIC NOT NULL, 22 km NUMERIC NOT NULL,
23 name VARCHAR(64), 23 name VARCHAR(64),
24 path VARCHAR(256) 24 path VARCHAR(256)
25 ); 25 );
26 SELECT AddGeometryColumn('river_axes_km', 'geom', 31466, 'POINT', 2); 26 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2);
27 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');
28 28
29 29
30 --Geodaesie/Querprofile/QP-Spuren/qps.shp 30 --Geodaesie/Querprofile/QP-Spuren/qps.shp
31 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; 31 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
35 km NUMERIC NOT NULL, 35 km NUMERIC NOT NULL,
36 z NUMERIC NOT NULL DEFAULT 0, 36 z NUMERIC NOT NULL DEFAULT 0,
37 name VARCHAR(64), 37 name VARCHAR(64),
38 path VARCHAR(256) 38 path VARCHAR(256)
39 ); 39 );
40 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2); 40 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2);
41 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');
42 42
43 43
44 -- Geodaesie/Linien/rohre-und-spreen 44 -- Geodaesie/Linien/rohre-und-spreen
45 CREATE SEQUENCE LINES_ID_SEQ; 45 CREATE SEQUENCE LINES_ID_SEQ;
49 kind VARCHAR(16) NOT NULL, 49 kind VARCHAR(16) NOT NULL,
50 z NUMERIC DEFAULT 0, 50 z NUMERIC DEFAULT 0,
51 name VARCHAR(64), 51 name VARCHAR(64),
52 path VARCHAR(256) 52 path VARCHAR(256)
53 ); 53 );
54 SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4); 54 SELECT AddGeometryColumn('lines', 'geom', 31467, 'LINESTRING', 3);
55 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');
56 -- 'kind': 56 -- 'kind':
57 -- 0: ROHR1 57 -- 0: ROHR1
58 -- 1: DAMM 58 -- 1: DAMM
59 59
64 id int PRIMARY KEY NOT NULL, 64 id int PRIMARY KEY NOT NULL,
65 river_id int REFERENCES rivers(id), 65 river_id int REFERENCES rivers(id),
66 name VARCHAR(256), 66 name VARCHAR(256),
67 path VARCHAR(256) 67 path VARCHAR(256)
68 ); 68 );
69 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2); 69 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2);
70 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');
71 71
72 72
73 -- Geodaesie/Festpunkte/Festpunkte.shp 73 -- Geodaesie/Festpunkte/Festpunkte.shp
74 CREATE SEQUENCE FIXPOINTS_ID_SEQ; 74 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
80 km NUMERIC NOT NULL, 80 km NUMERIC NOT NULL,
81 HPGP VARCHAR(2), 81 HPGP VARCHAR(2),
82 name VARCHAR(64), 82 name VARCHAR(64),
83 path VARCHAR(256) 83 path VARCHAR(256)
84 ); 84 );
85 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2); 85 SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2);
86 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');
87 87
88 88
89 -- Hydrologie/Hydr. Grenzen/talaue.shp 89 -- Hydrologie/Hydr. Grenzen/talaue.shp
90 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; 90 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
92 id int PRIMARY KEY NOT NULL, 92 id int PRIMARY KEY NOT NULL,
93 river_id int REFERENCES rivers(id), 93 river_id int REFERENCES rivers(id),
94 name VARCHAR(64), 94 name VARCHAR(64),
95 path VARCHAR(256) 95 path VARCHAR(256)
96 ); 96 );
97 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'POLYGON', 2); 97 SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2);
98 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');
99 99
100 100
101 -- Geodaesie/Hoehenmodelle/* 101 -- Geodaesie/Hoehenmodelle/*
102 CREATE SEQUENCE DEM_ID_SEQ; 102 CREATE SEQUENCE DEM_ID_SEQ;
119 river_id int REFERENCES rivers(id), 119 river_id int REFERENCES rivers(id),
120 area NUMERIC, 120 area NUMERIC,
121 name VARCHAR(256), 121 name VARCHAR(256),
122 path VARCHAR(256) 122 path VARCHAR(256)
123 ); 123 );
124 SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2); 124 SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2);
125 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');
126 126
127 127
128 --Hydrologie/HW-Schutzanlagen/hws.shp 128 --Hydrologie/HW-Schutzanlagen/hws.shp
129 CREATE SEQUENCE HWS_ID_SEQ; 129 CREATE SEQUENCE HWS_ID_SEQ;
133 hws_facility VARCHAR(256), 133 hws_facility VARCHAR(256),
134 type VARCHAR(256), 134 type VARCHAR(256),
135 name VARCHAR(64), 135 name VARCHAR(64),
136 path VARCHAR(256) 136 path VARCHAR(256)
137 ); 137 );
138 SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2); 138 SELECT AddGeometryColumn('hws','geom',31467,'LINESTRING',2);
139 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');
140 140
141 141
142 -- 142 --
143 --Hydrologie/UeSG 143 --Hydrologie/UeSG
159 count int, 159 count int,
160 area real, 160 area real,
161 perimeter real, 161 perimeter real,
162 path VARCHAR(256) 162 path VARCHAR(256)
163 ); 163 );
164 SELECT AddGeometryColumn('floodmaps', 'geom', 31466, 'MULTIPOLYGON', 2); 164 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
165 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom; 165 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
166 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);
167 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); 167 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
168 168
169 169
173 river_id int REFERENCES rivers(id), 173 river_id int REFERENCES rivers(id),
174 name VARCHAR(255), 174 name VARCHAR(255),
175 kind int, 175 kind int,
176 path VARCHAR(256) 176 path VARCHAR(256)
177 ); 177 );
178 SELECT AddGeometryColumn('hydr_boundaries','geom',31466,'LINESTRING',4); 178 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3);
179 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ'); 179 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ');
180 180
181 181
182 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; 182 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
183 CREATE TABLE hydr_boundaries_poly ( 183 CREATE TABLE hydr_boundaries_poly (
185 river_id int REFERENCES rivers(id), 185 river_id int REFERENCES rivers(id),
186 name VARCHAR(255), 186 name VARCHAR(255),
187 kind int, 187 kind int,
188 path VARCHAR(256) 188 path VARCHAR(256)
189 ); 189 );
190 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31466,'POLYGON',4); 190 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'POLYGON',3);
191 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ'); 191 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');
192 192
193 193
194 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; 194 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
195 CREATE TABLE gauge_location ( 195 CREATE TABLE gauge_location (
196 id int PRIMARY KEY NOT NULL, 196 id int PRIMARY KEY NOT NULL,
197 river_id int REFERENCES rivers(id), 197 river_id int REFERENCES rivers(id),
198 name VARCHAR(255), 198 name VARCHAR(255),
199 path VARCHAR(256) 199 path VARCHAR(256)
200 ); 200 );
201 SELECT AddGeometryColumn('gauge_location','geom',31466,'POINT',2); 201 SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2);
202 ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ'); 202 ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ');
203 203
204 COMMIT; 204 COMMIT;
http://dive4elements.wald.intevation.org