Mercurial > dive4elements > river
comparison flys-backend/doc/schema/postgresql-spatial.sql @ 4930:28f992c0f937 dami
SCHEMA CHANGES: Add tables for Hochwasserschutzanlagen
This adds the first draft of Hochwasserschutzanlagen tables
hws_points and hws_lines which replace the old hws table and
also lookup tables fed_states and hws_kinds.
| author | Andre Heinecke <aheinecke@intevation.de> |
|---|---|
| date | Mon, 28 Jan 2013 17:42:18 +0100 |
| parents | f5912365619c |
| children | f5c55d7ea07e |
comparison
equal
deleted
inserted
replaced
| 4887:1f6e544f7a7f | 4930:28f992c0f937 |
|---|---|
| 130 path VARCHAR(256) | 130 path VARCHAR(256) |
| 131 ); | 131 ); |
| 132 SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2); | 132 SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2); |
| 133 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ'); | 133 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ'); |
| 134 | 134 |
| 135 | 135 -- Static lookup tables for Hochwasserschutzanlagen |
| 136 --Hydrologie/HW-Schutzanlagen/hws.shp | 136 CREATE TABLE hws_kinds ( |
| 137 CREATE SEQUENCE HWS_ID_SEQ; | 137 id int PRIMARY KEY NOT NULL, |
| 138 CREATE TABLE hws ( | 138 kind VARCHAR(64) NOT NULL |
| 139 id int PRIMARY KEY NOT NULL, | 139 ); |
| 140 river_id int REFERENCES rivers(id), | 140 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass'); |
| 141 hws_facility VARCHAR(256), | 141 INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm'); |
| 142 type VARCHAR(256), | 142 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben'); |
| 143 name VARCHAR(64), | 143 |
| 144 path VARCHAR(256) | 144 CREATE TABLE fed_states ( |
| 145 ); | 145 id int PRIMARY KEY NOT NULL, |
| 146 SELECT AddGeometryColumn('hws','geom',31467,'LINESTRING',2); | 146 name VARCHAR(23) NOT NULL |
| 147 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); | 147 ); |
| 148 | 148 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern'); |
| 149 INSERT INTO fed_states (id, name) VALUES (2, 'Hessen'); | |
| 150 INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen'); | |
| 151 INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen'); | |
| 152 INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz'); | |
| 153 INSERT INTO fed_states (id, name) VALUES (6, 'Saarland'); | |
| 154 INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein'); | |
| 155 INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg'); | |
| 156 INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern'); | |
| 157 INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen'); | |
| 158 INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg'); | |
| 159 INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt'); | |
| 160 INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen'); | |
| 161 INSERT INTO fed_states (id, name) VALUES (14, 'Berlin'); | |
| 162 INSERT INTO fed_states (id, name) VALUES (15, 'Bremen'); | |
| 163 INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg'); | |
| 164 | |
| 165 --Hydrologie/HW-Schutzanlagen/*Linien.shp | |
| 166 CREATE SEQUENCE HWS_LINES_ID_SEQ; | |
| 167 CREATE TABLE hws_lines ( | |
| 168 id int PRIMARY KEY NOT NULL, | |
| 169 ogr_fid int, | |
| 170 kind_id int REFERENCES hws_kinds(id) DEFAULT 2, | |
| 171 fed_state_id int REFERENCES fed_states(id), | |
| 172 river_id int REFERENCES rivers(id), | |
| 173 name VARCHAR(256), | |
| 174 path VARCHAR(256), | |
| 175 offical INT DEFAULT 0, | |
| 176 agency VARCHAR(256), | |
| 177 range VARCHAR(256), | |
| 178 shore_side INT DEFAULT 0, | |
| 179 source VARCHAR(256), | |
| 180 status_date TIMESTAMP, | |
| 181 description VARCHAR(256) | |
| 182 ); | |
| 183 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'LINESTRING', 2); | |
| 184 SELECT AddGeometryColumn('hws_lines', 'geom_target', 31467, 'LINESTRING', 2); -- ? | |
| 185 SELECT AddGeometryColumn('hws_lines', 'geom_rated_level', 31467, 'LINESTRING', 2); -- ? | |
| 186 -- TODO: dike_km_from dike_km_to, are they geometries? | |
| 187 | |
| 188 ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ'); | |
| 189 | |
| 190 --Hydrologie/HW-Schutzanlagen/*Punkte.shp | |
| 191 CREATE SEQUENCE HWS_POINTS_ID_SEQ; | |
| 192 CREATE TABLE hws_points ( | |
| 193 id int PRIMARY KEY NOT NULL, | |
| 194 ogr_fid int, | |
| 195 kind_id int REFERENCES hws_kinds(id) DEFAULT 2, | |
| 196 fed_state_id int REFERENCES fed_states(id), | |
| 197 river_id int REFERENCES rivers(id), | |
| 198 name VARCHAR, | |
| 199 path VARCHAR, | |
| 200 offical INT DEFAULT 0, | |
| 201 agency VARCHAR, | |
| 202 range VARCHAR, | |
| 203 shore_side INT DEFAULT 0, | |
| 204 source VARCHAR, | |
| 205 status_date VARCHAR, | |
| 206 description VARCHAR, | |
| 207 freeboard FLOAT8, | |
| 208 dike_km FLOAT8, | |
| 209 z FLOAT8, | |
| 210 z_target FLOAT8, | |
| 211 rated_level FLOAT8 | |
| 212 ); | |
| 213 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2); | |
| 214 | |
| 215 ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ'); | |
| 149 | 216 |
| 150 -- | 217 -- |
| 151 --Hydrologie/UeSG | 218 --Hydrologie/UeSG |
| 152 -- | 219 -- |
| 153 -- 'kind' can be one of: | 220 -- 'kind' can be one of: |