Mercurial > gemma

comparison wamos.sql @ 94:611dc09dbcd8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add gauge measurement attributes.
author Tom Gottfried <tom@intevation.de>
date Fri, 01 Jun 2018 20:43:06 +0200
parents 765906789840
children c79fd71ebe06
comparison
equal deleted inserted replaced
93:765906789840 94:611dc09dbcd8
56 CREATE TABLE wamos.responsibility_areas ( 56 CREATE TABLE wamos.responsibility_areas (
57 country char(2) PRIMARY KEY REFERENCES countries, 57 country char(2) PRIMARY KEY REFERENCES countries,
58 area geometry(POLYGON, 4326) 58 area geometry(POLYGON, 4326)
59 ); 59 );
60 60
61 CREATE TABLE wamos.languages ( 61 CREATE TABLE wamos.language_codes (
62 language_code varchar PRIMARY KEY 62 language_code varchar PRIMARY KEY
63 ); 63 );
64 64
65 CREATE TABLE wamos.user_profiles ( 65 CREATE TABLE wamos.user_profiles (
66 username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger 66 username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger
67 country char(2) NOT NULL REFERENCES countries, 67 country char(2) NOT NULL REFERENCES countries,
68 language_code varchar REFERENCES languages, 68 language_code varchar REFERENCES language_codes,
69 map_extent box2d, 69 map_extent box2d,
70 email_adress varchar NOT NULL UNIQUE 70 email_adress varchar NOT NULL UNIQUE
71 ); 71 );
72 72
73 CREATE TABLE wamos.templates ( 73 CREATE TABLE wamos.templates (
160 value int NOT NULL 160 value int NOT NULL
161 ); 161 );
162 162
163 CREATE TABLE wamos_fairway.gauge_measurements ( 163 CREATE TABLE wamos_fairway.gauge_measurements (
164 fk_gauge_id isrs NOT NULL REFERENCES gauges, 164 fk_gauge_id isrs NOT NULL REFERENCES gauges,
165 measure_date timestamp with time zone NOT NULL,
166 PRIMARY KEY (fk_gauge_id, measure_date),
165 -- XXX: Is country_code really relevant for WAMOS or just NtS? 167 -- XXX: Is country_code really relevant for WAMOS or just NtS?
166 -- country_code char(2) NOT NULL REFERENCES countries, 168 -- country_code char(2) NOT NULL REFERENCES countries,
167 -- TODO: add relations to stuff provided as enumerations 169 -- TODO: add relations to stuff provided as enumerations
168 dummy_attrib varchar, 170 sender varchar NOT NULL, -- "from" attribute from DRC
169 "..." varchar 171 language_code varchar NOT NULL REFERENCES language_codes,
170 -- TODO: add real gauge measurement attributes (DRC 2.1.5) 172 date_issue timestamp with time zone NOT NULL,
171 ); 173 reference_code varchar(4) NOT NULL REFERENCES depth_references,
174 -- XXX: Always ZPG?
175 water_level double precision NOT NULL,
176 predicted boolean NOT NULL,
177 is_waterlevel boolean NOT NULL,
178 -- XXX: "measure_code" if really only W or Q
179 -- XXX: Do we need "unit" attribute or can we normalise on import?
180 value_min double precision, -- XXX: NOT NULL if predicted?
181 value_max double precision, -- XXX: NOT NULL if predicted?
182 --- TODO: Add a double range type for checking?
183 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
184 source_organization varchar NOT NULL -- "originator"
185 );
186 CREATE TRIGGER gauge_measurements_date_info BEFORE UPDATE ON gauge_measurements
187 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
172 188
173 CREATE TABLE wamos_waterway.waterway_axis ( 189 CREATE TABLE wamos_waterway.waterway_axis (
174 wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY, 190 wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY,
175 -- TODO: Do we need to check data set quality as described in DRC 2.1.6? 191 -- TODO: Do we need to check data set quality as described in DRC 2.1.6?
176 objnam varchar NOT NULL, 192 objnam varchar NOT NULL,