Mercurial > gemma
annotate schema/updates/1200/01.st_as_resp_area.sql @ 4393:cdd332dbebbf
Fixed date in filename for stretch download.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Thu, 12 Sep 2019 20:24:03 +0200 |
parents | 5e38667f740c |
children |
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 $$; |