Mercurial > gemma
changeset 87:c46fb3f1faeb
Add distance mark attributes and try to consolidate.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 01 Jun 2018 19:12:31 +0200 |
parents | 2477cba141e9 |
children | a96fee9654db |
files | wamos.sql |
diffstat | 1 files changed, 17 insertions(+), 12 deletions(-) [+] |
line wrap: on
line diff
--- a/wamos.sql Fri Jun 01 18:37:31 2018 +0200 +++ b/wamos.sql Fri Jun 01 19:12:31 2018 +0200 @@ -159,17 +159,27 @@ distance_mark_function varchar(8) PRIMARY KEY ); +CREATE TABLE wamos_waterway.position_codes ( + position_code char(2) PRIMARY KEY + -- Use smallint because of fairway availability provided on daily basis? + -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx, + -- sheet "Position_code" or RIS-Index encoding guide? + -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here. + -- Clarify! + -- TODO: Do we need an attribute "meaning" or so? + ); + -- This table allows linkage between the 1D ISRS location codes and 2D space -- e.g. for cutting bottleneck area out of waterway area based on virtual -- distance marks along waterway axis (see SUC7). CREATE TABLE wamos_waterway.distance_marks ( location_code isrs PRIMARY KEY, - geom geometry(POINT, 4326), - distance_mark_function varchar(8) REFERENCES distance_mark_functions, - -- TODO: add relations to stuff provided as enumerations - dummy_attrib varchar, - "..." varchar - -- TODO: add real distance mark attributes (DRC 2.1.7) + geom geometry(POINT, 4326) NOT NULL, + distance_mark_function varchar(8) + NOT NULL REFERENCES distance_mark_functions, + -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem + -- to encode the same thing as the object code in ISRS location code. + position_code char(2) NOT NULL REFERENCES position_codes ); CREATE TABLE wamos_waterway.sections_stretches ( @@ -273,11 +283,6 @@ -- -- Fairway availability -- -CREATE TABLE wamos_fairway.spot_marks ( - mark_name varchar PRIMARY KEY - -- Use smallint because of fairway availability provided on daily basis? - ); - CREATE TABLE wamos_fairway.levels_of_service ( level_of_service smallint PRIMARY KEY ); @@ -295,7 +300,7 @@ CREATE TABLE wamos_fairway.fairway_availability ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, - position varchar REFERENCES spot_marks, + position_code char(2) REFERENCES position_codes, bottleneck_id varchar NOT NULL REFERENCES bottlenecks, surdat date NOT NULL, UNIQUE (bottleneck_id, surdat),