gemma: schema/gemma.sql annotate

annotate schema/gemma.sql @ 2781:a2127495093e

Prevent duplicate waterway axis and area geometries Duplicate axis geometries can prevent finding a contiguous axis between two distance marks in the current implementation of ISRSrange_axis(). Although duplicate area geometries did not show to be harmful up to now, they do not seem to be useful and generally duplicate geometries have the potential to make debugging of any geometry processing harder.

author	Tom Gottfried <tom@intevation.de>
date	Fri, 22 Mar 2019 14:35:32 +0100
parents	ab1a22052437
children	3b1bdbbc0225

rev	line source
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	3
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	6
2172 7e2c77ccc02f Removed trigger to update date_info on gauge_measurements. Sascha Wilde <wilde@intevation.de> parents: 2170 diff changeset	7 -- Copyright (C) 2018,2019 by via donau
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	10
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	11 -- Author(s):
1301 2304778c4432 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1298 diff changeset	12 -- * Tom Gottfried <tom@intevation.de>
1336 f65d1767452c add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1301 diff changeset	13 -- * Sascha Teichmann <sascha.teichmann@intevation.de>
2172 7e2c77ccc02f Removed trigger to update date_info on gauge_measurements. Sascha Wilde <wilde@intevation.de> parents: 2170 diff changeset	14 -- * Sascha Wilde <sascha.wilde@intevation.de>
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	15
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	16 BEGIN;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	17
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	18 --
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	19 -- Extensions
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	20 --
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	21 CREATE EXTENSION postgis;
2083 6deafd6f7f86 Fix exclusion constraint for waterway profiles Tom Gottfried <tom@intevation.de> parents: 2076 diff changeset	22 -- needed for multi-column GiST indexes with otherwise unsupported types:
6deafd6f7f86 Fix exclusion constraint for waterway profiles Tom Gottfried <tom@intevation.de> parents: 2076 diff changeset	23 CREATE EXTENSION btree_gist;
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	24
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	25 --
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	26 -- Trigger functions
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	27 --
96 d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	28 -- TODO: will there ever be UPDATEs or can we drop that function due to
d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	29 -- historicisation?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	30 CREATE FUNCTION update_date_info() RETURNS trigger
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	31 LANGUAGE plpgsql
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	32 AS $$
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	33 BEGIN
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	34 NEW.date_info = CURRENT_TIMESTAMP;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	35 RETURN NEW;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	36 END;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	37 $$;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	38
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	39 -- Trigger function to be used as a constraint trigger to enforce uniqueness
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	40 -- of geometries in the column with its name given as an argument to the
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	41 -- trigger function
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	42 CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	43 $$
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	44 DECLARE
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	45 new_geom geometry;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	46 has_equal boolean;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	47 BEGIN
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	48 EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0])
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	49 INTO new_geom
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	50 USING NEW;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	51 EXECUTE format(
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	52 'SELECT bool_or(ST_Equals($1, CAST(%I AS geometry))) FROM %I.%I '
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	53 'WHERE id <> $2',
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	54 TG_ARGV[0], TG_TABLE_SCHEMA, TG_TABLE_NAME)
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	55 INTO has_equal
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	56 USING new_geom, NEW.id;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	57 IF has_equal THEN
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	58 RAISE EXCEPTION
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	59 'new row for relation "%" violates constraint trigger "%"',
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	60 TG_TABLE_NAME, TG_NAME
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	61 USING
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	62 DETAIL = format('Failing row contains geometry in %s',
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	63 Box2D(new_geom)),
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	64 ERRCODE = 23505,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	65 SCHEMA = TG_TABLE_SCHEMA,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	66 TABLE = TG_TABLE_NAME,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	67 COLUMN = TG_ARGV[0],
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	68 CONSTRAINT = TG_NAME;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	69 END IF;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	70 RETURN NEW;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	71 END;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	72 $$
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	73 LANGUAGE plpgsql;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	74
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	75 --
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	76 -- GEMMA data
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	77 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	78
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	79 -- Namespace not to be accessed directly by any user
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	80 CREATE SCHEMA internal
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	81 -- Profile data are only accessible via the view users.list_users.
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	82 CREATE TABLE user_profiles (
361 f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 345 diff changeset	83 username varchar PRIMARY KEY CHECK(octet_length(username) <= 63),
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 345 diff changeset	84 -- keep username length compatible with role identifier
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	85 map_extent box2d NOT NULL,
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	86 email_address varchar NOT NULL
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	87 )
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	88 -- Columns referencing user-visible schemas added below.
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	89 ;
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	90
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	91
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	92 -- Namespace to be accessed by sys_admin only
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	93 CREATE SCHEMA sys_admin
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	94 CREATE TABLE system_config (
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	95 config_key varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	96 config_val varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	97 )
437 b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	98
478 3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	99 CREATE TABLE password_reset_requests (
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	100 hash varchar(32) PRIMARY KEY,
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	101 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	102 username varchar NOT NULL
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	103 REFERENCES internal.user_profiles(username)
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	104 ON DELETE CASCADE ON UPDATE CASCADE
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	105 )
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	106
624 8772979f8750 Remove test data from schema Tom Gottfried <tom@intevation.de> parents: 611 diff changeset	107 -- Tables with geo data to be published with GeoServer.
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	108 CREATE TABLE external_services (
437 b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	109 local_name varchar PRIMARY KEY,
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	110 remote_url varchar NOT NULL,
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	111 is_wfs boolean NOT NULL DEFAULT TRUE
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	112 )
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	113
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	114 CREATE TABLE published_services (
598 4854a1e85870 Ensure published service is based on existing table Tom Gottfried <tom@intevation.de> parents: 580 diff changeset	115 name regclass PRIMARY KEY,
1288 9f7dc950ffd2 Accept only well-formed XML documents as style in database Tom Gottfried <tom@intevation.de> parents: 1194 diff changeset	116 style xml CHECK(style IS DOCUMENT),
437 b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	117 as_wms boolean NOT NULL DEFAULT TRUE,
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	118 as_wfs boolean NOT NULL DEFAULT TRUE
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	119 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	120 ;
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	121
acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	122 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	123 -- Look-up tables with data that are static in a running system
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	124 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	125 CREATE TABLE language_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	126 language_code varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	127 );
84 d905022a48e9 More user attributes from APUC3. Tom Gottfried <tom@intevation.de> parents: 83 diff changeset	128
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	129 CREATE TABLE catccls (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	130 catccl smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	131 -- TODO: Do we need name and/or definition from IENC feature catalogue?
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	132 -- (see page 328 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	133 );
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	134 INSERT INTO catccls VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
85 1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	135
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	136 CREATE TABLE dirimps (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	137 dirimp smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	138 -- TODO: Do we need name and/or definition from IENC feature catalogue?
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	139 -- (see page 381 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	140 );
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	141 INSERT INTO dirimps VALUES (1), (2), (3), (4);
48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	142 -- dirimp_5 left out because it cannot be used for waterway area
85 1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	143
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	144 CREATE TABLE depth_references (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	145 depth_reference varchar(4) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	146 -- See col. AB and AI RIS-Index Encoding Guide
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	147 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	148 -- RIS-Index) and other references (e.g. col. AB and AI):
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	149 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	150 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	151 -- _ Do not mixup things with different meanings in one table at all
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	152 -- (which would mean a model differing a bit from RIS-Index ideas)
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	153 );
93 765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	154
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	155 CREATE TABLE catdis (
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	156 catdis smallint PRIMARY KEY
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	157 -- TODO: Do we need name and/or definition from IENC feature catalogue?
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	158 -- (see page 171 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	159 );
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	160 INSERT INTO catdis VALUES (1), (2), (3), (4);
78 012948f18faa Distance marks as link between 1D and 2D. Tom Gottfried <tom@intevation.de> parents: 77 diff changeset	161
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	162 CREATE TABLE position_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	163 position_code char(2) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	164 -- Use smallint because of fairway availability provided on daily basis?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	165 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	166 -- sheet "Position_code" or RIS-Index encoding guide?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	167 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here.
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	168 -- Clarify!
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	169 -- TODO: Do we need an attribute "meaning" or so?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	170 );
87 c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	171
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	172 CREATE TABLE levels_of_service (
1662 d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	173 level_of_service smallint PRIMARY KEY,
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	174 name varchar(4)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	175 );
1662 d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	176 INSERT INTO levels_of_service (
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	177 level_of_service,
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	178 name
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	179 ) VALUES (1, 'LOS1'), (2, 'LOS2'), (3, 'LOS3');
89 a36bfec5edd3 Move more tables to waterway schema to tweak diagrams. Tom Gottfried <tom@intevation.de> parents: 88 diff changeset	180
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	181 CREATE TABLE riverbed_materials (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	182 material varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	183 -- XXX: Should this table contain choices from DRC 2.2.3 or
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	184 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	185 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	186
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	187 CREATE TABLE survey_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	188 survey_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	189 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	190
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	191 CREATE TABLE coverage_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	192 coverage_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	193 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	194
577 e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	195 CREATE TABLE limiting_factors (
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	196 limiting_factor varchar PRIMARY KEY
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	197 );
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	198
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	199 CREATE TABLE measure_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	200 measure_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	201 );
58 30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	202
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	203
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	204 -- Namespace for user management related data
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	205 CREATE SCHEMA users
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	206 CREATE TABLE responsibility_areas (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	207 country char(2) PRIMARY KEY REFERENCES countries,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	208 area geography(MULTIPOLYGON, 4326)
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	209 CHECK(ST_IsValid(CAST(area AS geometry)))
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	210 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	211
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	212 CREATE TABLE templates (
2367 0aedae39726e Print templates: Removed primary key from templates table and add a unique constraint to template name and country instead to make possible that different countries can use templates with same name. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2341 diff changeset	213 template_name varchar NOT NULL,
2267 37ae1bee3e4a Ajjusted RLS for user templates. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2230 diff changeset	214 country char(2) REFERENCES countries,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	215 template_data bytea NOT NULL,
2367 0aedae39726e Print templates: Removed primary key from templates table and add a unique constraint to template name and country instead to make possible that different countries can use templates with same name. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2341 diff changeset	216 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
0aedae39726e Print templates: Removed primary key from templates table and add a unique constraint to template name and country instead to make possible that different countries can use templates with same name. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2341 diff changeset	217 UNIQUE (template_name, country)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	218 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	219 CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	220 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	221 ;
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	222 ALTER TABLE internal.user_profiles ADD
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	223 country char(2) NOT NULL REFERENCES users.responsibility_areas;
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	224
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	225
831 fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	226 -- Namespace for system wide configuration
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	227 CREATE SCHEMA systemconf
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	228 CREATE TABLE feature_colours (
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	229 feature_name varchar,
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	230 style_attr varchar,
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	231 r int NOT NULL CHECK (r >= 0 AND r < 256),
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	232 g int NOT NULL CHECK (g >= 0 AND g < 256),
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	233 b int NOT NULL CHECK (b >= 0 AND b < 256),
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	234 a numeric NOT NULL CHECK (a >= 0 AND a <= 1),
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	235 PRIMARY KEY (feature_name, style_attr)
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	236 )
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	237 ;
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	238
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	239 -- Namespace for waterway data that can change in a running system
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	240 CREATE SCHEMA waterway
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	241
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	242 -- Eventually obsolete.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	243 -- See https://roundup-intern.intevation.de/gemma/issue5
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	244 -- CREATE TABLE rwdrs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	245 -- tretch isrsrange PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	246 -- -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	247 -- -- Only B-tree supports UNIQUE indexes, but we need the GIST index
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	248 -- -- below anyhow.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	249 -- -- Is it a good idea to build B-tree indexes on relatively large
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	250 -- -- composites of string values or should we use inter PKs?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	251 -- -- => In case the index is used and cache space becomes a limiting
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	252 -- -- factor, this might be an issue.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	253 -- rwdr double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	254 -- EXCLUDE USING GIST (stretch WITH &&)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	255 --)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	256
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	257 CREATE TABLE waterway_area (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	258 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	259 area geography(POLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	260 CHECK(ST_IsValid(CAST(area AS geometry))),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	261 catccl smallint REFERENCES catccls,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	262 dirimp smallint REFERENCES dirimps
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	263 )
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	264 CREATE CONSTRAINT TRIGGER waterway_area_area_unique
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	265 AFTER INSERT OR UPDATE OF area ON waterway_area
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	266 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('area')
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	267
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	268
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	269 CREATE TABLE gauges (
1825 484ae4fe09d7 Backed out changeset 332e42a2088d (no staging needed). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1824 diff changeset	270 location isrs PRIMARY KEY CHECK(
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	271 (location).orc SIMILAR TO 'G[[:digit:]]{4}'
642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	272 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	273 objname varchar NOT NULL,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	274 geom geography(POINT, 4326) NOT NULL,
1826 d4e2637eed58 Schema: Adjust gauges once again to be in sync with RIS index. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1825 diff changeset	275 applicability_from_km int8,
d4e2637eed58 Schema: Adjust gauges once again to be in sync with RIS index. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1825 diff changeset	276 applicability_to_km int8,
d4e2637eed58 Schema: Adjust gauges once again to be in sync with RIS index. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1825 diff changeset	277 validity tstzrange,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	278 -- pasted text from a more general specification is given
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	279 -- (a gauge is not a berth!)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	280 -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	281 zero_point double precision NOT NULL,
2369 89a3096e1988 Schema: Dropped wrong constraints in gauges.geodref. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2367 diff changeset	282 geodref varchar,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	283 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
1826 d4e2637eed58 Schema: Adjust gauges once again to be in sync with RIS index. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1825 diff changeset	284 source_organization varchar
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	285 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	286 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	287 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	288
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	289 CREATE TABLE gauges_reference_water_levels (
1825 484ae4fe09d7 Backed out changeset 332e42a2088d (no staging needed). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1824 diff changeset	290 gauge_id isrs NOT NULL REFERENCES gauges,
2318 06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	291 -- Omit foreign key constraint to be able to store not NtS-compliant
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	292 -- names, too:
2317 8a8680e70d2e Cleanup schema for reference water levels Tom Gottfried <tom@intevation.de> parents: 2300 diff changeset	293 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
8a8680e70d2e Cleanup schema for reference water levels Tom Gottfried <tom@intevation.de> parents: 2300 diff changeset	294 PRIMARY KEY (gauge_id, depth_reference),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	295 value int NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	296 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	297
2451 960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	298 CREATE VIEW gauges_geoserver AS
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	299 SELECT
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	300 g.location,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	301 isrs_asText(g.location) AS isrs_code,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	302 g.objname,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	303 g.geom,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	304 g.applicability_from_km,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	305 g.applicability_to_km,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	306 g.validity,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	307 g.zero_point,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	308 g.geodref,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	309 g.date_info,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	310 g.source_organization,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	311 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	312 r.value))
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	313 AS reference_water_levels
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	314 FROM gauges g LEFT JOIN LATERAL (
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	315 SELECT gauge_id, depth_reference, value
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	316 FROM gauges_reference_water_levels
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	317 ) r ON r.gauge_id = g.location
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	318 GROUP BY g.location
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	319
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	320 CREATE TABLE gauge_measurements (
1636 37ee25bc2bbe Updated database schema for gauge meaurement imports. Raimund Renkert <raimund.renkert@intevation.de> parents: 1583 diff changeset	321 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1825 484ae4fe09d7 Backed out changeset 332e42a2088d (no staging needed). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1824 diff changeset	322 fk_gauge_id isrs NOT NULL REFERENCES gauges,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	323 measure_date timestamp with time zone NOT NULL,
1636 37ee25bc2bbe Updated database schema for gauge meaurement imports. Raimund Renkert <raimund.renkert@intevation.de> parents: 1583 diff changeset	324 country_code char(2) NOT NULL REFERENCES countries,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	325 -- TODO: add relations to stuff provided as enumerations
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	326 sender varchar NOT NULL, -- "from" attribute from DRC
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	327 language_code varchar NOT NULL REFERENCES language_codes,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	328 date_issue timestamp with time zone NOT NULL,
1775 fcb0106ec510 Gauge measurement import: Added reference_code column. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1759 diff changeset	329 reference_code varchar(4) NOT NULL REFERENCES depth_references,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	330 water_level double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	331 predicted boolean NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	332 is_waterlevel boolean NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	333 -- XXX: "measure_code" if really only W or Q
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	334 -- XXX: Do we need "unit" attribute or can we normalise on import?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	335 value_min double precision, -- XXX: NOT NULL if predicted?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	336 value_max double precision, -- XXX: NOT NULL if predicted?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	337 --- TODO: Add a double range type for checking?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	338 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
1780 48791416bea5 (Approved) gauge measurement import: Fixed row level security. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1775 diff changeset	339 source_organization varchar NOT NULL, -- "originator"
1783 8fd132b9cdbd Gauge measurements: Add an unique constraint so there could only Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1780 diff changeset	340 staging_done boolean NOT NULL DEFAULT false,
8fd132b9cdbd Gauge measurements: Add an unique constraint so there could only Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1780 diff changeset	341 -- So we can have a staged and
8fd132b9cdbd Gauge measurements: Add an unique constraint so there could only Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1780 diff changeset	342 -- a non-staged fk_gauge_id/measure_date pair.
8fd132b9cdbd Gauge measurements: Add an unique constraint so there could only Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1780 diff changeset	343 UNIQUE (fk_gauge_id, measure_date, staging_done)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	344 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	345
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	346 CREATE TABLE waterway_axis (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	347 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	348 wtwaxs geography(LINESTRING, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	349 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	350 -- TODO: Do we need to check data set quality (DRC 2.1.6)?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	351 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	352 nobjnam varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	353 )
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	354 CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	355 AFTER INSERT OR UPDATE OF wtwaxs ON waterway_axis
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	356 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs')
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	357
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	358 -- This table allows linkage between 1D ISRS location codes and 2D space
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	359 -- e.g. for cutting bottleneck area out of waterway area based on virtual
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	360 -- distance marks along waterway axis (see SUC7).
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	361 CREATE TABLE distance_marks_virtual (
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	362 location_code isrs PRIMARY KEY,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	363 geom geography(POINT, 4326) NOT NULL,
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	364 related_enc varchar(12) NOT NULL
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	365 )
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	366
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	367 CREATE TABLE distance_marks (
1861 5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	368 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	369 country char(2) REFERENCES countries,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	370 hectom int,
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	371 geom geography(POINT, 4326) NOT NULL,
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	372 -- include location in primary key, because we have no fairway code:
1861 5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	373 catdis smallint REFERENCES catdis,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	374 position_code char(2) REFERENCES position_codes,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	375 related_enc varchar(12)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	376 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	377
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	378 -- A table to help geoserver serve the distance marks as WFS 1.1.0.
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	379 -- At least geoserver-2.13.2 does not serve type geography correctly
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	380 -- and does not serve the location_code as isrs type
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	381 CREATE VIEW distance_marks_geoserver AS
2170 3bfe48e32f20 Fixed de-duplication (primary geoserver id) for virt dist marks. Sascha Wilde <wilde@intevation.de> parents: 2159 diff changeset	382 SELECT location_code,
3bfe48e32f20 Fixed de-duplication (primary geoserver id) for virt dist marks. Sascha Wilde <wilde@intevation.de> parents: 2159 diff changeset	383 isrs_asText(location_code) AS location,
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	384 geom::Geometry(POINT, 4326),
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	385 related_enc,
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	386 (location_code).hectometre
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	387 FROM distance_marks_virtual
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	388
2300 3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	389 CREATE VIEW distance_marks_ashore_geoserver AS
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	390 SELECT id,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	391 country,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	392 geom::Geometry(POINT, 4326),
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	393 related_enc,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	394 hectom,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	395 catdis,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	396 position_code
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	397 FROM distance_marks
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	398
1446 1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	399 -- We need to configure primary keys for the views used by
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	400 -- geoserver for wfs, otherwise it will generate ids on the fly,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	401 -- which will change for the same feature...
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	402 -- See
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	403 -- https://docs.geoserver.org/stable/en/user/data/database/primarykey.html
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	404 -- for details.
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	405 CREATE TABLE gt_pk_metadata (
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	406 table_schema VARCHAR(32) NOT NULL,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	407 table_name VARCHAR(32) NOT NULL,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	408 pk_column VARCHAR(32) NOT NULL,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	409 pk_column_idx INTEGER,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	410 pk_policy VARCHAR(32),
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	411 pk_sequence VARCHAR(64),
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	412 unique (table_schema, table_name, pk_column),
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	413 check (pk_policy in ('sequence', 'assigned', 'autogenerated'))
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	414 )
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	415
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	416 CREATE TABLE stretches (
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	417 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	418 name varchar NOT NULL,
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	419 stretch isrsrange NOT NULL,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	420 area geography(MULTIPOLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	421 CHECK(ST_IsValid(CAST(area AS geometry))),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	422 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	423 nobjnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	424 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
345 b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	425 source_organization varchar NOT NULL,
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	426 staging_done boolean NOT NULL DEFAULT false,
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	427 UNIQUE(name, staging_done)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	428 )
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	429
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	430 CREATE TABLE stretch_countries (
1906 32c56e6c089a Stretch import: Added forgotten source file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1894 diff changeset	431 stretches_id int NOT NULL REFERENCES stretches(id)
32c56e6c089a Stretch import: Added forgotten source file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1894 diff changeset	432 ON DELETE CASCADE,
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	433 country_code char(2) NOT NULL REFERENCES countries(country_code),
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	434 UNIQUE(stretches_id, country_code)
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	435 )
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	436
1929 f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	437 -- Published view for GeoServer
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	438 CREATE VIEW stretches_geoserver AS SELECT
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	439 id,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	440 name,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	441 (stretch).lower::varchar as lower,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	442 (stretch).upper::varchar as upper,
1983 f9f1babe52ae Fix area generation from multipolygon input Tom Gottfried <tom@intevation.de> parents: 1975 diff changeset	443 area::Geometry(MULTIPOLYGON, 4326),
1929 f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	444 objnam,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	445 nobjnam,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	446 date_info,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	447 source_organization,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	448 (SELECT string_agg(country_code, ', ')
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	449 FROM stretch_countries
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	450 WHERE stretches_id = id) AS countries,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	451 staging_done
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	452 FROM stretches
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	453
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	454
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	455 CREATE TRIGGER sections_stretches_date_info
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	456 BEFORE UPDATE ON stretches
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	457 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	458
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	459 CREATE TABLE waterway_profiles (
2072 b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	460 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
2141 3f58809d2beb Waterway profiles have to match a distance mark Tom Gottfried <tom@intevation.de> parents: 2130 diff changeset	461 location isrs NOT NULL REFERENCES distance_marks_virtual,
3f58809d2beb Waterway profiles have to match a distance mark Tom Gottfried <tom@intevation.de> parents: 2130 diff changeset	462 geom geography(linestring, 4326),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	463 validity tstzrange,
2072 b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	464 lnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	465 mwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	466 hnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	467 fe30 double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	468 fe100 double precision,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	469 -- XXX: further normalise using reference_water_levels?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	470 CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	471 OR validity IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	472 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
345 b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	473 source_organization varchar NOT NULL,
2072 b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	474 staging_done boolean NOT NULL DEFAULT false,
2130 f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	475 EXCLUDE USING GIST (
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	476 isrs_asText(location) WITH =,
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	477 validity WITH &&,
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	478 CAST(staging_done AS int) WITH =)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	479 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	480 CREATE TRIGGER waterway_profiles_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	481 BEFORE UPDATE ON waterway_profiles
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	482 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	483
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	484 CREATE TABLE fairway_dimensions (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	485 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	486 area geography(POLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	487 CHECK(ST_IsValid(CAST(area AS geometry))),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	488 level_of_service smallint NOT NULL REFERENCES levels_of_service,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	489 min_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	490 max_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	491 min_depth smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	492 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
345 b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	493 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	494 staging_done boolean NOT NULL DEFAULT false
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	495 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	496 CREATE TRIGGER fairway_dimensions_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	497 BEFORE UPDATE ON fairway_dimensions
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	498 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	499
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	500 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	501 -- Bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	502 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	503 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	504 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	505 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	506 CREATE TABLE bottlenecks (
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	507 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	508 bottleneck_id varchar UNIQUE NOT NULL,
1825 484ae4fe09d7 Backed out changeset 332e42a2088d (no staging needed). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1824 diff changeset	509 fk_g_fid isrs NOT NULL REFERENCES gauges,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	510 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	511 objnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	512 nobjnm varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	513 stretch isrsrange NOT NULL,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	514 area geography(MULTIPOLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	515 CHECK(ST_IsValid(CAST(area AS geometry))),
2765 41f5ac76d642 BT import: allow for rb and lb to be null. Sascha Wilde <wilde@intevation.de> parents: 2627 diff changeset	516 rb char(2) REFERENCES countries, -- from rb_lb in interface
41f5ac76d642 BT import: allow for rb and lb to be null. Sascha Wilde <wilde@intevation.de> parents: 2627 diff changeset	517 lb char(2) REFERENCES countries, -- from rb_lb in interface
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	518 responsible_country char(2) NOT NULL REFERENCES countries,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	519 revisiting_time smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	520 limiting varchar NOT NULL REFERENCES limiting_factors,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	521 -- surtyp varchar NOT NULL REFERENCES survey_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	522 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	523 -- coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	524 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	525 -- CHECK allowed combinations of surtyp and coverage or
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	526 -- different model approach?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	527 -- depth_reference char(3) NOT NULL REFERENCES depth_references,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	528 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	529 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	530 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	531 -- additional_data xml -- Currently not relevant for GEMMA
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	532 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	533 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	534 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	535 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	536
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	537 CREATE TABLE bottlenecks_riverbed_materials (
2076 0e006077bbfa Add missing NOT NULL constraints Tom Gottfried <tom@intevation.de> parents: 2072 diff changeset	538 bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
0e006077bbfa Add missing NOT NULL constraints Tom Gottfried <tom@intevation.de> parents: 2072 diff changeset	539 riverbed varchar NOT NULL REFERENCES riverbed_materials,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	540 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	541 PRIMARY KEY (bottleneck_id, riverbed)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	542 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	543
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	544 CREATE TABLE sounding_results (
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	545 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	546 bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	547 date_info date NOT NULL,
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	548 UNIQUE (bottleneck_id, date_info),
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	549 area geography(POLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	550 CHECK(ST_IsValid(CAST(area AS geometry))),
611 effd22c0ae5a Sounding result: Write simple SQL insert dumper. Not deterministic, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 609 diff changeset	551 surtyp varchar REFERENCES survey_types,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	552 coverage varchar REFERENCES coverage_types,
2444 7ca6bdb2d174 depth_reference in sounding data must be more permissive. Sascha Wilde <wilde@intevation.de> parents: 2396 diff changeset	553 depth_reference varchar(4) NOT NULL, -- REFERENCES depth_references,
1131 2e6b47cdb2ca Store octrees along with sounding results Tom Gottfried <tom@intevation.de> parents: 1085 diff changeset	554 octree_checksum varchar,
2e6b47cdb2ca Store octrees along with sounding results Tom Gottfried <tom@intevation.de> parents: 1085 diff changeset	555 octree_index bytea,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	556 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	557 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	558
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	559 CREATE TABLE sounding_results_contour_lines (
1133 dd4071019676 Delete contour lines with their sounding result Tom Gottfried <tom@intevation.de> parents: 1131 diff changeset	560 sounding_result_id int NOT NULL REFERENCES sounding_results
dd4071019676 Delete contour lines with their sounding result Tom Gottfried <tom@intevation.de> parents: 1131 diff changeset	561 ON DELETE CASCADE,
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	562 height numeric NOT NULL,
919 271561dce2e6 Store contour lines in 2D Tom Gottfried <tom@intevation.de> parents: 917 diff changeset	563 lines geography(multilinestring, 4326) NOT NULL,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	564 -- TODO: generate valid simple features and add constraint:
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	565 -- CHECK(ST_IsSimple(CAST(lines AS geometry))),
1133 dd4071019676 Delete contour lines with their sounding result Tom Gottfried <tom@intevation.de> parents: 1131 diff changeset	566 PRIMARY KEY (sounding_result_id, height)
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	567 )
821 d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	568 -- A view to help geoserver serve contour lines.
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	569 -- At least geoserver-2.13.2 does not serve type geography correctly
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	570 CREATE VIEW sounding_results_contour_lines_geoserver AS
821 d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	571 SELECT bottleneck_id,
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	572 date_info,
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	573 height,
919 271561dce2e6 Store contour lines in 2D Tom Gottfried <tom@intevation.de> parents: 917 diff changeset	574 CAST(lines AS geometry(multilinestring, 4326)) AS lines
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	575 FROM sounding_results_contour_lines cl
74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	576 JOIN sounding_results sr
821 d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	577 ON sr.id = cl.sounding_result_id
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	578
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	579 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	580 -- Fairway availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	581 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	582 CREATE TABLE fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	583 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	584 position_code char(2) REFERENCES position_codes,
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	585 bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	586 surdat date NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	587 UNIQUE (bottleneck_id, surdat),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	588 -- additional_data xml -- Currently not relevant for GEMMA
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	589 critical boolean,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	590 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	591 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	592 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	593 CREATE TRIGGER fairway_availability_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	594 BEFORE UPDATE ON fairway_availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	595 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	596
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	597 CREATE TABLE fa_reference_values (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	598 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	599 level_of_service smallint NOT NULL REFERENCES levels_of_service,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	600 PRIMARY KEY (fairway_availability_id, level_of_service),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	601 fairway_depth smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	602 fairway_width smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	603 fairway_radius int,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	604 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	605 IS NOT NULL),
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	606 shallowest_spot geography(POINT, 4326)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	607 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	608
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	609 CREATE TABLE bottleneck_pdfs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	610 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	611 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	612 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	613 PRIMARY KEY (fairway_availability_id, profile_pdf_url),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	614 pdf_generation_date timestamp with time zone NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	615 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	616 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	617
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	618 CREATE TABLE effective_fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	619 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	620 measure_date timestamp with time zone NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	621 level_of_service smallint NOT NULL REFERENCES levels_of_service,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	622 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	623 available_depth_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	624 available_width_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	625 water_level_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	626 CHECK(COALESCE(available_depth_value, available_width_value,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	627 water_level_value) IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	628 measure_type varchar NOT NULL REFERENCES measure_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	629 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	630 forecast_generation_time timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	631 CHECK(measure_type <> 'forecasted'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	632 OR forecast_generation_time IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	633 value_lifetime timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	634 CHECK(measure_type = 'minimum guaranteed'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	635 OR value_lifetime IS NOT NULL)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	636 )
1085 72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	637
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	638 CREATE VIEW bottleneck_overview AS
1085 72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	639 SELECT
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	640 objnam AS name,
2558 802d2a62b4bc schema: fix bottleneck_overview's project Bernhard Reiter <bernhard@intevation.de> parents: 2542 diff changeset	641 ST_Centroid(area)::Geometry(POINT, 4326) AS point,
1085 72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	642 (lower(stretch)).hectometre AS from,
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	643 (upper(stretch)).hectometre AS to,
1520 6ad1f431bc85 Fixed date of latest measurement in bottlenecks list. Sascha Wilde <wilde@intevation.de> parents: 1446 diff changeset	644 sr.current::text
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	645 FROM bottlenecks bn LEFT JOIN (
74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	646 SELECT bottleneck_id, max(date_info) AS current FROM sounding_results
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	647 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id
1085 72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	648 ORDER BY objnam
2396 6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	649
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	650 -- Published view for GeoServer
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	651 CREATE VIEW bottlenecks_geoserver AS
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	652 WITH fairway_availability_latest AS (
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	653 SELECT DISTINCT ON (bottleneck_id) bottleneck_id,date_info,critical
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	654 FROM fairway_availability
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	655 ORDER BY bottleneck_id, date_info DESC NULLS LAST),
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	656 gauge_measurements_waterlevel AS (
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	657 SELECT DISTINCT ON (fk_gauge_id)
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	658 fk_gauge_id, measure_date, predicted, water_level
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	659 FROM gauge_measurements WHERE predicted ='false'
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	660 ORDER BY fk_gauge_id, measure_date DESC NULLS LAST)
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	661 SELECT
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	662 b.id,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	663 b.bottleneck_id,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	664 b.objnam,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	665 b.nobjnm,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	666 b.stretch,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	667 b.area,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	668 b.rb,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	669 b.lb,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	670 b.responsible_country,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	671 b.revisiting_time,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	672 b.limiting,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	673 b.date_info,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	674 b.source_organization,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	675 g.location AS gauge_isrs_code,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	676 g.objname AS gauge_objname,
2770 ab1a22052437 Handle the case of missing depth references for bottlenecks gracefully. Sascha Wilde <wilde@intevation.de> parents: 2765 diff changeset	677 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
ab1a22052437 Handle the case of missing depth references for bottlenecks gracefully. Sascha Wilde <wilde@intevation.de> parents: 2765 diff changeset	678 r.value))
ab1a22052437 Handle the case of missing depth references for bottlenecks gracefully. Sascha Wilde <wilde@intevation.de> parents: 2765 diff changeset	679 AS reference_water_levels,
2396 6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	680 fal.date_info AS fa_date_info,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	681 fal.critical AS fa_critical,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	682 gmw.water_level as gm_waterlevel
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	683 FROM bottlenecks b LEFT JOIN gauges g ON b.fk_g_fid = g.location
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	684 LEFT JOIN LATERAL (
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	685 SELECT gauge_id,depth_reference,value
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	686 FROM gauges_reference_water_levels
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	687 ) r ON r.gauge_id = b.fk_g_fid
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	688 LEFT JOIN LATERAL (
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	689 SELECT bottleneck_id,date_info,critical
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	690 FROM fairway_availability_latest
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	691 WHERE b.id=bottleneck_id) fal ON TRUE
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	692 LEFT JOIN LATERAL (
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	693 SELECT water_level
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	694 FROM gauge_measurements_waterlevel
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	695 WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	696 GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level;
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	697 ;
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	698
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	699 -- Configure primary keys for geoserver views
2085 bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	700 INSERT INTO waterway.gt_pk_metadata VALUES
2451 960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	701 ('waterway', 'gauges_geoserver', 'location'),
2170 3bfe48e32f20 Fixed de-duplication (primary geoserver id) for virt dist marks. Sascha Wilde <wilde@intevation.de> parents: 2159 diff changeset	702 ('waterway', 'distance_marks_geoserver', 'location_code'),
2300 3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	703 ('waterway', 'distance_marks_ashore_geoserver', 'id'),
2341 fb353ff08377 Added geoserver config for stretches layer. Sascha Wilde <wilde@intevation.de> parents: 2327 diff changeset	704 ('waterway', 'bottlenecks_geoserver', 'id'),
fb353ff08377 Added geoserver config for stretches layer. Sascha Wilde <wilde@intevation.de> parents: 2327 diff changeset	705 ('waterway', 'stretches_geoserver', 'id');
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	706
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	707 --
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	708 -- Import queue and respective logging
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	709 --
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	710 CREATE TYPE import_state AS ENUM (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	711 'queued',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	712 'running',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	713 'failed', 'unchanged', 'pending',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	714 'accepted', 'declined'
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	715 );
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	716
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	717 CREATE TYPE log_type AS ENUM ('info', 'warn', 'error');
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	718
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	719 -- Namespace for import queue and respective logging
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	720 CREATE SCHEMA import
1547 d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	721
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	722 CREATE TABLE import_configuration (
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	723 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	724 username varchar NOT NULL
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	725 REFERENCES internal.user_profiles(username)
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	726 ON DELETE CASCADE
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	727 ON UPDATE CASCADE,
2042 d29ac997eb34 This breaks this branch!!!! Starting to remove the old persistent layer for configured imports. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1995 diff changeset	728 kind varchar NOT NULL
1547 d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	729 )
1702 49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	730
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	731 CREATE TABLE import_configuration_attributes (
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	732 import_configuration_id int NOT NULL
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	733 REFERENCES import_configuration(id)
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	734 ON DELETE CASCADE
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	735 ON UPDATE CASCADE,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	736 k VARCHAR NOT NULL,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	737 v TEXT NOT NULL,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	738 UNIQUE (import_configuration_id, k)
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	739 )
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	740
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	741 CREATE TABLE imports (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	742 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	743 state import_state NOT NULL DEFAULT 'queued',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	744 kind varchar NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	745 enqueued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	746 due timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	747 retry_wait interval
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	748 CHECK(retry_wait IS NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	749 OR retry_wait >= interval '0 microseconds'),
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	750 trys_left int, -- if NULL and retry_wait NOT NULL, endless
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	751 username varchar NOT NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	752 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	753 ON DELETE CASCADE
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	754 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	755 signer varchar
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	756 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	757 ON DELETE SET NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	758 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	759 send_email boolean NOT NULL DEFAULT false,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	760 data TEXT,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	761 summary TEXT
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	762 )
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	763
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	764 CREATE INDEX enqueued_idx ON imports(enqueued, state)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	765
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	766 CREATE TABLE import_logs (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	767 import_id int NOT NULL REFERENCES imports(id)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	768 ON DELETE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	769 time timestamp NOT NULL DEFAULT now(),
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	770 kind log_type NOT NULL DEFAULT 'info',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	771 msg TEXT NOT NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	772 )
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	773
2627 3a242e6aa56d Import log: Add filter for log entries with warnings only: GET /api/imports?warnings=true Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2589 diff changeset	774 CREATE INDEX kind_idx ON import_logs(kind)
3a242e6aa56d Import log: Add filter for log entries with warnings only: GET /api/imports?warnings=true Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2589 diff changeset	775
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	776 CREATE TABLE track_imports (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	777 import_id int NOT NULL REFERENCES imports(id)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	778 ON DELETE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	779 relation regclass NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	780 key int NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	781 UNIQUE (relation, key)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	782 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	783 ;
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	784
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	785 CREATE FUNCTION import.del_import(imp_id int) RETURNS void AS
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	786 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	787 DECLARE
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	788 tmp RECORD;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	789 BEGIN
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	790 FOR tmp IN
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	791 SELECT * FROM import.track_imports WHERE import_id = imp_id
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	792 LOOP
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	793 EXECUTE format('DELETE FROM %s WHERE id = $1', tmp.relation) USING tmp.key;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	794 END LOOP;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	795 END;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	796 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	797 LANGUAGE plpgsql;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	798
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	799 CREATE FUNCTION import.del_import() RETURNS trigger AS
1172 c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	800 $$
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	801 BEGIN
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	802 EXECUTE format('DELETE FROM %s WHERE id = $1', OLD.relation) USING OLD.key;
1354 1000e0e71d5a Added missing rights and fixed trigger function. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1338 diff changeset	803 RETURN NULL;
1172 c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	804 END;
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	805 $$
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	806 LANGUAGE plpgsql;
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	807
2542 fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	808 CREATE SCHEMA caching
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	809
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	810 CREATE TABLE sounding_differences (
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	811 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	812 minuend int NOT NULL REFERENCES waterway.sounding_results(id)
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	813 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	814 subtrahend int NOT NULL REFERENCES waterway.sounding_results(id)
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	815 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	816 UNIQUE (minuend, subtrahend)
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	817 )
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	818
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	819 CREATE TABLE sounding_differences_contour_lines (
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	820 sounding_differences_id int NOT NULL REFERENCES sounding_differences(id)
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	821 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	822 height numeric NOT NULL,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	823 lines geography(multilinestring, 4326) NOT NULL,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	824 PRIMARY KEY (sounding_differences_id, height)
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	825 )
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	826 ;
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	827
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	828 CREATE VIEW waterway.sounding_differences AS SELECT
2589 f4c399a496cb Morphological differences: Add SRID to cast in ios-lines view. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2558 diff changeset	829 sd.id AS id,
f4c399a496cb Morphological differences: Add SRID to cast in ios-lines view. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2558 diff changeset	830 bn.objnam AS objnam,
f4c399a496cb Morphological differences: Add SRID to cast in ios-lines view. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2558 diff changeset	831 srm.date_info AS minuend,
f4c399a496cb Morphological differences: Add SRID to cast in ios-lines view. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2558 diff changeset	832 srs.date_info AS subtrahend,
f4c399a496cb Morphological differences: Add SRID to cast in ios-lines view. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2558 diff changeset	833 sdcl.height AS height,
f4c399a496cb Morphological differences: Add SRID to cast in ios-lines view. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2558 diff changeset	834 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
2542 fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	835 FROM
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	836 caching.sounding_differences sd JOIN
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	837 caching.sounding_differences_contour_lines sdcl ON sd.id = sdcl.sounding_differences_id JOIN
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	838 waterway.sounding_results srm ON sd.minuend = srm.id JOIN
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	839 waterway.sounding_results srs ON sd.subtrahend = srs.id JOIN
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	840 waterway.bottlenecks bn ON srm.bottleneck_id = bn.id;
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	841
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	842 COMMIT;

Mercurial > gemma

annotate schema/gemma.sql @ 2781:a2127495093e