Mercurial > gemma

comparison wamos.sql @ 80:004198a3cbc0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Set correct EPSG code.
author Tom Gottfried <tom@intevation.de>
date Thu, 31 May 2018 16:30:20 +0200
parents c22ddffc2071
children acaa485c0c1e
comparison
equal deleted inserted replaced
79:c22ddffc2071 80:004198a3cbc0
104 -- This table allows linkage between the 1D ISRS location codes and 2D space 104 -- This table allows linkage between the 1D ISRS location codes and 2D space
105 -- e.g. for cutting bottleneck area out of waterway area based on virtual 105 -- e.g. for cutting bottleneck area out of waterway area based on virtual
106 -- distance marks along waterway axis (see SUC7). 106 -- distance marks along waterway axis (see SUC7).
107 CREATE TABLE wamos.distance_marks ( 107 CREATE TABLE wamos.distance_marks (
108 location_code isrs PRIMARY KEY, 108 location_code isrs PRIMARY KEY,
109 geom geometry(POINT, 3146), 109 geom geometry(POINT, 4326),
110 distance_mark_function varchar(8) REFERENCES distance_mark_functions, 110 distance_mark_function varchar(8) REFERENCES distance_mark_functions,
111 -- TODO: add relations to stuff provided as enumerations 111 -- TODO: add relations to stuff provided as enumerations
112 dummy_attrib varchar, 112 dummy_attrib varchar,
113 "..." varchar 113 "..." varchar
114 -- TODO: add real distance mark attributes (DRC 2.1.7) 114 -- TODO: add real distance mark attributes (DRC 2.1.7)
169 fk_g_fid isrs NOT NULL REFERENCES gauges, 169 fk_g_fid isrs NOT NULL REFERENCES gauges,
170 -- XXX: DRC references "ch. 3.1.1", which does not exist in document. 170 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
171 objnam varchar, 171 objnam varchar,
172 nobjnm varchar, 172 nobjnm varchar,
173 stretch isrsrange NOT NULL, 173 stretch isrsrange NOT NULL,
174 area geometry(POLYGON, 3146) NOT NULL, 174 area geometry(POLYGON, 4326) NOT NULL,
175 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface 175 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
176 lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface 176 lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
177 responsible_country char(2) NOT NULL REFERENCES countries, 177 responsible_country char(2) NOT NULL REFERENCES countries,
178 revisiting_time smallint NOT NULL, 178 revisiting_time smallint NOT NULL,
179 limiting varchar NOT NULL REFERENCES limiting_factors, 179 limiting varchar NOT NULL REFERENCES limiting_factors,
202 202
203 CREATE TABLE wamos.sounding_results ( 203 CREATE TABLE wamos.sounding_results (
204 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, 204 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
205 date_info date NOT NULL, 205 date_info date NOT NULL,
206 PRIMARY KEY (bottleneck_id, date_info), 206 PRIMARY KEY (bottleneck_id, date_info),
207 area geometry(POLYGON, 3146) NOT NULL, 207 area geometry(POLYGON, 4326) NOT NULL,
208 surtyp varchar NOT NULL REFERENCES survey_types, 208 surtyp varchar NOT NULL REFERENCES survey_types,
209 coverage varchar REFERENCES coverage_types, 209 coverage varchar REFERENCES coverage_types,
210 depth_reference char(3) NOT NULL REFERENCES depth_references, 210 depth_reference char(3) NOT NULL REFERENCES depth_references,
211 sounding_data raster NOT NULL, 211 sounding_data raster NOT NULL,
212 staging_done boolean NOT NULL DEFAULT false 212 staging_done boolean NOT NULL DEFAULT false
256 PRIMARY KEY (fairway_availability_id, level_of_service), 256 PRIMARY KEY (fairway_availability_id, level_of_service),
257 fairway_depth smallint, 257 fairway_depth smallint,
258 fairway_width smallint, 258 fairway_width smallint,
259 fairway_radius int, 259 fairway_radius int,
260 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL), 260 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL),
261 shallowest_spot geometry(POINT, 3146) 261 shallowest_spot geometry(POINT, 4326)
262 ); 262 );
263 263
264 CREATE TABLE wamos.bottleneck_pdfs ( 264 CREATE TABLE wamos.bottleneck_pdfs (
265 fairway_availability_id int NOT NULL REFERENCES fairway_availability, 265 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
266 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL 266 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL