Mercurial > gemma

annotate schema/updates/1200/01.st_as_resp_area.sql @ 5666:37c2354a6024 clickable-links

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Render links only to known bottlenecks
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 05 Dec 2023 15:34:31 +0100
parents 5e38667f740c
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
2 -- without warranty, see README.md and license for details.
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
3
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
6
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
7 -- Copyright (C) 2019 by via donau
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
9 -- Software engineering by Intevation GmbH
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
10
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
11 -- Author(s):
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
12 -- * Sascha Wilde <sascha.wilde@intevation.de>
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
13
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
14 DROP TABLE users.responsibility_areas CASCADE;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
15
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
16 ALTER TABLE waterway.stretches SET SCHEMA users;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
17 ALTER TABLE waterway.stretch_countries SET SCHEMA users;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
18
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
19 ALTER TABLE users.stretch_countries
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
20 RENAME COLUMN stretches_id TO stretch_id;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
21 ALTER TABLE users.stretch_countries
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
22 RENAME COLUMN country_code TO country;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
23
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
24 ALTER TABLE internal.user_profiles
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
25 ADD CONSTRAINT user_profiles_country_fkey
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
26 FOREIGN KEY (country) REFERENCES countries;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
27
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
28 --
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
29 -- geoserver view:
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
30
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
31 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
32 SELECT
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
33 s.id,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
34 s.name,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
35 (s.stretch).lower::varchar as lower,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
36 (s.stretch).upper::varchar as upper,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
37 s.area::Geometry(MULTIPOLYGON, 4326),
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
38 s.objnam,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
39 s.nobjnam,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
40 s.date_info,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
41 s.source_organization,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
42 (SELECT string_agg(country, ', ')
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
43 FROM users.stretch_countries
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
44 WHERE stretch_id = s.id) AS countries,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
45 s.staging_done,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
46 min(g.gm_measuredate) AS gm_measuredate,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
47 min(g.gm_n_14d) AS gm_n_14d,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
48 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
49 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
50 FROM users.stretches s
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
51 LEFT JOIN waterway.gauges_geoserver g
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
52 ON isrs_fromtext(g.isrs_code) <@ s.stretch
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
53 GROUP BY s.id;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
54
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
55 --
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
56 -- auth:
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
57
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
58 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA users TO sys_admin;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
59
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
60
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
61 --
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
62 -- manage_users:
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
63
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
64 CREATE OR REPLACE FUNCTION users.current_user_area_utm()
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
65 RETURNS geometry
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
66 AS $$
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
67 DECLARE utm_area geometry;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
68 BEGIN
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
69 SELECT ST_Union(ST_Transform(area::geometry, z))
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
70 INTO STRICT utm_area
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
71 FROM (SELECT area,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
72 best_utm(ST_Collect(area::geometry) OVER ()) AS z
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
73 FROM users.stretches st
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
74 JOIN users.stretch_countries stc
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
75 ON stc.stretch_id = st.id
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
76 WHERE country = (SELECT country
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
77 FROM users.list_users
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
78 WHERE username = current_user)) AS st;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
79 RETURN utm_area;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
80 END;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
81 $$
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
82 LANGUAGE plpgsql
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
83 STABLE PARALLEL SAFE;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
84
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
85 CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
86 AS $$
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
87 BEGIN
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
88 IF NEW.map_extent IS NULL
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
89 THEN
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
90 NEW.map_extent = ST_Extent(CAST(area AS geometry))
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
91 FROM users.stretches st
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
92 JOIN users.stretch_countries stc ON stc.stretch_id = st.id
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
93 WHERE stc.country = NEW.country;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
94 END IF;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
95 INSERT INTO internal.user_profiles (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
96 username, country, map_extent, email_address)
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
97 VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address);
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
98 EXECUTE format(
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
99 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
100 NEW.username,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
101 NEW.rolname,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
102 internal.check_password(NEW.pw));
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
103
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
104 -- Do not leak new password
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
105 NEW.pw = '';
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
106 RETURN NEW;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
107 END;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
108 $$
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
109 LANGUAGE plpgsql
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
110 SECURITY DEFINER;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
111
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
112
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
113 --
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
114 -- search_functions:
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
115
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
116 CREATE OR REPLACE FUNCTION search_stretches(search_string text) RETURNS jsonb
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
117 LANGUAGE plpgsql STABLE PARALLEL SAFE
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
118 AS $$
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
119 DECLARE
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
120 _result jsonb;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
121 BEGIN
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
122 SELECT COALESCE(json_agg(r),'[]')
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
123 INTO _result
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
124 FROM (SELECT id,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
125 objnam AS name,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
126 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
127 'stretch' AS type
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
128 FROM users.stretches
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
129 WHERE objnam ILIKE '%' || search_string || '%'
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
130 OR nobjnam ILIKE '%' || search_string || '%'
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
131 ORDER BY name) r;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
132 RETURN _result;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
133 END;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
134 $$;