Mercurial > gemma

comparison schema/gemma.sql @ 944:feef06db5d9d geo-style

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged default into geo-style branch.
author Sascha L. Teichmann <teichmann@intevation.de>
date Wed, 10 Oct 2018 00:16:31 +0200
parents e758e12b38c9 e6220a19f284
children 5f89868bd75e
comparison
equal deleted inserted replaced
933:7899867c7bf5 944:feef06db5d9d
105 105
106 CREATE TABLE reference_water_levels ( 106 CREATE TABLE reference_water_levels (
107 reference_water_level varchar(20) PRIMARY KEY 107 reference_water_level varchar(20) PRIMARY KEY
108 ); 108 );
109 109
110 CREATE TABLE distance_mark_functions ( 110 CREATE TABLE catdis (
111 -- XXX: Redundant information to object code in isrs code of dist. mark 111 catdis smallint PRIMARY KEY
112 distance_mark_function varchar(8) PRIMARY KEY 112 -- TODO: Do we need name and/or definition from IENC feature catalogue?
113 ); 113 -- (see page 171 of edition 2.3)
114 );
115 INSERT INTO catdis VALUES (1), (2), (3), (4);
114 116
115 CREATE TABLE position_codes ( 117 CREATE TABLE position_codes (
116 position_code char(2) PRIMARY KEY 118 position_code char(2) PRIMARY KEY
117 -- Use smallint because of fairway availability provided on daily basis? 119 -- Use smallint because of fairway availability provided on daily basis?
118 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx, 120 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
285 -- e.g. for cutting bottleneck area out of waterway area based on virtual 287 -- e.g. for cutting bottleneck area out of waterway area based on virtual
286 -- distance marks along waterway axis (see SUC7). 288 -- distance marks along waterway axis (see SUC7).
287 CREATE TABLE distance_marks ( 289 CREATE TABLE distance_marks (
288 location_code isrs PRIMARY KEY, 290 location_code isrs PRIMARY KEY,
289 geom geography(POINT, 4326) NOT NULL, 291 geom geography(POINT, 4326) NOT NULL,
290 distance_mark_function varchar(8) 292 catdis smallint NOT NULL REFERENCES catdis,
291 NOT NULL REFERENCES distance_mark_functions,
292 -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem
293 -- to encode the same thing as the object code in ISRS location code.
294 position_code char(2) NOT NULL REFERENCES position_codes 293 position_code char(2) NOT NULL REFERENCES position_codes
295 ) 294 )
296 295
297 -- A table to help geoserver serve the distance marks as WFS 1.1.0. 296 -- A table to help geoserver serve the distance marks as WFS 1.1.0.
298 -- At least geoserver-2.13.2 does not serve type geography correctly 297 -- At least geoserver-2.13.2 does not serve type geography correctly
299 -- and does not serve the location_code as isrs type 298 -- and does not serve the location_code as isrs type
300 CREATE VIEW waterway.distance_marks_geoserver AS 299 CREATE VIEW waterway.distance_marks_geoserver AS
301 SELECT location_code::VARCHAR, 300 SELECT location_code::VARCHAR,
302 geom::Geometry(POINT, 4326), 301 geom::Geometry(POINT, 4326),
303 distance_mark_function, 302 catdis,
304 position_code, 303 position_code,
305 (location_code).hectometre 304 (location_code).hectometre
306 FROM waterway.distance_marks 305 FROM waterway.distance_marks
307 306
308 CREATE TABLE sections_stretches ( 307 CREATE TABLE sections_stretches (