gemma: wamos.sql annotate

annotate wamos.sql @ 50:a7cb0c2c3dd7

An area will be processed on upload for bottlenecks (see SUC8).

author	Tom Gottfried <tom@intevation.de>
date	Mon, 07 May 2018 23:01:12 +0200
parents	521e5add3748
children	bb81404e8ad1

rev	line source
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	1 BEGIN;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	2
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	3 CREATE EXTENSION postgis;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	4
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	5 CREATE FUNCTION update_date_info() RETURNS trigger
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	6 LANGUAGE plpgsql
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	7 AS $$
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	8 BEGIN
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	9 NEW.date_info = CURRENT_TIMESTAMP;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	10 RETURN NEW;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	11 END;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	12 $$;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	13
37 6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	14 -- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	15 -- See RIS-Index Encoding Guide
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	16 CREATE TYPE isrs AS (
37 6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	17 country_code char(2), -- ISO 3166 country code
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	18 -- could be validated against countries table.
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	19 locode char(3), -- without the country code:
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	20 -- http://www.unece.org/cefact/locode/welcome.html
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	21 fairway_section char(5),
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	22 object_reference char(5),
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	23 hectometre int -- should be constrained to five digits
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	24 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	25
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	26 CREATE TYPE isrsrange AS RANGE (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	27 subtype = isrs
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	28 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	29
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	30 CREATE TABLE rwdrs (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	31 stretch isrsrange PRIMARY KEY,
37 6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	32 -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	33 -- Only B-tree supports UNIQUE indexes, but we need the GIST index
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	34 -- below anyhow.
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	35 -- Is it a good idea to build B-tree indexes on relatively large
37 6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	36 -- composites of string values or should we use inter PKs?
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	37 -- => In case the index is used and cache space becomes a limiting
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	38 -- factor, this might be an issue.
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	39 rwdr double precision NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	40 EXCLUDE USING GIST (stretch WITH &&)
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	41 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	42
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	43 CREATE TABLE gauges (
38 529319bc3c5b Mark gauges table as placeholder. Tom Gottfried <tom@intevation.de> parents: 37 diff changeset	44 location isrs PRIMARY KEY,
43 e84ff54eb8e9 Tweak ER diagramm. Tom Gottfried <tom@intevation.de> parents: 42 diff changeset	45 dummy_attrib varchar,
e84ff54eb8e9 Tweak ER diagramm. Tom Gottfried <tom@intevation.de> parents: 42 diff changeset	46 "..." varchar
38 529319bc3c5b Mark gauges table as placeholder. Tom Gottfried <tom@intevation.de> parents: 37 diff changeset	47 -- TODO: add real gauge attributes (DRC 2.1.4)
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	48 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	49
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	50 CREATE TABLE countries (
37 6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	51 country_code char(2) PRIMARY KEY -- ISO 3166 country code
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	52 -- A smallint PK would require even less disk space i.e. on the FK side.
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	53 -- This might be an issue in case cache space becomes a limiting
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	54 -- factor when there are many FKs pointing here.
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	55 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	56
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	57 CREATE TABLE riverbed_materials (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	58 material varchar PRIMARY KEY
39 830287983657 Comment: details to clarify riverbed material. Tom Gottfried <tom@intevation.de> parents: 38 diff changeset	59 -- XXX: Should this table contain choices from DRC 2.2.3 or
830287983657 Comment: details to clarify riverbed material. Tom Gottfried <tom@intevation.de> parents: 38 diff changeset	60 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	61 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	62
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	63 CREATE TABLE survey_types (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	64 survey_type varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	65 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	66
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	67 CREATE TABLE coverage_types (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	68 coverage_type varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	69 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	70
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	71 CREATE TABLE limiting_factors (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	72 limiting_factor varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	73 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	74
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	75 CREATE TABLE depth_references (
40 535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	76 depth_reference varchar(4) PRIMARY KEY
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	77 -- See col. AB and AI RIS-Index Encoding Guide
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	78 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	79 -- RIS-Index) and other references (e.g. col. AB and AI):
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	80 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	81 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	82 -- _ Do not mixup things with different meanings in one table at all
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	83 -- (which would mean a model differing a bit from RIS-Index ideas)
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	84 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	85
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	86 CREATE TABLE bottlenecks (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	87 bottleneck_id varchar PRIMARY KEY,
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	88 fk_g_fid isrs REFERENCES gauges,
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	89 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	90 objnam varchar UNIQUE NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	91 nobjnm varchar UNIQUE,
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	92 stretch isrsrange NOT NULL,
50 a7cb0c2c3dd7 An area will be processed on upload for bottlenecks (see SUC8). Tom Gottfried <tom@intevation.de> parents: 49 diff changeset	93 area geometry(POLYGON, 3146) NOT NULL,
42 47f1ffdc6ef4 Cleanup rb_lb. Tom Gottfried <tom@intevation.de> parents: 41 diff changeset	94 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
47f1ffdc6ef4 Cleanup rb_lb. Tom Gottfried <tom@intevation.de> parents: 41 diff changeset	95 lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
41 dd858a74b4e9 Set columns referencing countries next to each other. Tom Gottfried <tom@intevation.de> parents: 40 diff changeset	96 responsible_country char(2) NOT NULL REFERENCES countries,
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	97 revisiting_time smallint NOT NULL,
46 9106e1510244 Tweak ER diagramm. Tom Gottfried <tom@intevation.de> parents: 45 diff changeset	98 limiting varchar NOT NULL REFERENCES limiting_factors,
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	99 surtyp varchar NOT NULL REFERENCES survey_types,
45 cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	100 -- XXX: Also an attribut of sounding result?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	101 coverage varchar NOT NULL REFERENCES coverage_types,
45 cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	102 -- XXX: Also an attribut of sounding result?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	103 -- CHECK allowed combinations of surtyp and coverage or
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	104 -- different model approach?
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	105 depth_reference char(3) NOT NULL REFERENCES depth_references,
45 cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	106 -- XXX: Also an attribut of sounding result?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	107 date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
48 6185e4780017 Fix syntax error. Tom Gottfried <tom@intevation.de> parents: 47 diff changeset	108 source_organization varchar NOT NULL
47 a03ff9076969 Delivered by interface for other uses than WAMOS. Tom Gottfried <tom@intevation.de> parents: 46 diff changeset	109 -- additional_data xml -- Currently not relevant for WAMOS
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	110 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	111 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	112 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	113
44 dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	114 CREATE TABLE bottlenecks_riverbed_materials (
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	115 bottleneck_id varchar REFERENCES bottlenecks,
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	116 riverbed varchar REFERENCES riverbed_materials,
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	117 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	118 PRIMARY KEY (bottleneck_id, riverbed)
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	119 );
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	120
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	121 CREATE TABLE sounding_results (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	122 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	123 date_info date NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	124 PRIMARY KEY (bottleneck_id, date_info),
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	125 area int, -- XX: Check SRS/test data what this should be
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	126 type int, -- XX: Check SRS/test data what this should be
45 cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	127 surtyp varchar NOT NULL REFERENCES survey_types,
cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	128 coverage varchar NOT NULL REFERENCES coverage_types,
cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	129 depth_reference char(3) NOT NULL REFERENCES depth_references,
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	130 sounding_data raster NOT NULL
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	131 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	132
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	133 COMMIT;

Mercurial > gemma

annotate wamos.sql @ 50:a7cb0c2c3dd7