Mercurial > gemma
changeset 93:765906789840
Add gauge attributes.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 01 Jun 2018 20:25:21 +0200 |
parents | f11366b419ae |
children | 611dc09dbcd8 |
files | wamos.sql |
diffstat | 1 files changed, 26 insertions(+), 14 deletions(-) [+] |
line wrap: on
line diff
--- a/wamos.sql Fri Jun 01 20:08:47 2018 +0200 +++ b/wamos.sql Fri Jun 01 20:25:21 2018 +0200 @@ -118,16 +118,39 @@ dirimp smallint REFERENCES dirimps ); +CREATE TABLE wamos_fairway.depth_references ( + depth_reference varchar(4) PRIMARY KEY + -- See col. AB and AI RIS-Index Encoding Guide + -- XXX: We need a way to distinguish between geodetic (eg. col. AP + -- RIS-Index) and other references (e.g. col. AB and AI): + -- _ multi-column FK with a boolean column (geodetic/non-geodetic; + -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side. + -- _ Do not mixup things with different meanings in one table at all + -- (which would mean a model differing a bit from RIS-Index ideas) + ); + CREATE TABLE wamos_fairway.reference_water_levels ( reference_water_level varchar(20) PRIMARY KEY ); CREATE TABLE wamos_fairway.gauges ( location isrs PRIMARY KEY, - dummy_attrib varchar, - "..." varchar - -- TODO: add real gauge attributes (DRC 2.1.4) + function_code varchar(10) NOT NULL, -- XXX: What is this really for? + objname varchar NOT NULL, + is_left boolean, -- XXX: Or reference position_codes? + geom geometry(POINT, 4326) NOT NULL, + applicability isrsrange, + validity tstzrange, -- XXX: Should ranges be NOT NULL? In DRC, only copy + -- pasted text from a more general specification is given + -- (a gauge is not a berth!) + -- TODO: Ranges need a joint exclusion constaint to prevent overlaps? + zero_point double precision NOT NULL, + geodref varchar(4) REFERENCES depth_references, + date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + source_organization varchar NOT NULL ); +CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges + FOR EACH ROW EXECUTE PROCEDURE update_date_info(); CREATE TABLE wamos_fairway.gauges_reference_water_levels ( gauge_id isrs NOT NULL REFERENCES gauges, @@ -250,17 +273,6 @@ limiting_factor varchar PRIMARY KEY ); -CREATE TABLE wamos_fairway.depth_references ( - depth_reference varchar(4) PRIMARY KEY - -- See col. AB and AI RIS-Index Encoding Guide - -- XXX: We need a way to distinguish between geodetic (eg. col. AP - -- RIS-Index) and other references (e.g. col. AB and AI): - -- _ multi-column FK with a boolean column (geodetic/non-geodetic; - -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side. - -- _ Do not mixup things with different meanings in one table at all - -- (which would mean a model differing a bit from RIS-Index ideas) - ); - -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)