Mercurial > gemma
view schema/updates/1200/01.st_as_resp_area.sql @ 5677:a57be8bfb6ea sr-v2
More suited file names.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Sun, 11 Feb 2024 09:51:21 +0100 |
parents | 5e38667f740c |
children |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Sascha Wilde <sascha.wilde@intevation.de> DROP TABLE users.responsibility_areas CASCADE; ALTER TABLE waterway.stretches SET SCHEMA users; ALTER TABLE waterway.stretch_countries SET SCHEMA users; ALTER TABLE users.stretch_countries RENAME COLUMN stretches_id TO stretch_id; ALTER TABLE users.stretch_countries RENAME COLUMN country_code TO country; ALTER TABLE internal.user_profiles ADD CONSTRAINT user_profiles_country_fkey FOREIGN KEY (country) REFERENCES countries; -- -- geoserver view: CREATE OR REPLACE VIEW waterway.stretches_geoserver AS SELECT s.id, s.name, (s.stretch).lower::varchar as lower, (s.stretch).upper::varchar as upper, s.area::Geometry(MULTIPOLYGON, 4326), s.objnam, s.nobjnam, s.date_info, s.source_organization, (SELECT string_agg(country, ', ') FROM users.stretch_countries WHERE stretch_id = s.id) AS countries, s.staging_done, min(g.gm_measuredate) AS gm_measuredate, min(g.gm_n_14d) AS gm_n_14d, max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, max(g.forecast_accuracy_1d) AS forecast_accuracy_1d FROM users.stretches s LEFT JOIN waterway.gauges_geoserver g ON isrs_fromtext(g.isrs_code) <@ s.stretch GROUP BY s.id; -- -- auth: GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA users TO sys_admin; -- -- manage_users: CREATE OR REPLACE FUNCTION users.current_user_area_utm() RETURNS geometry AS $$ DECLARE utm_area geometry; BEGIN SELECT ST_Union(ST_Transform(area::geometry, z)) INTO STRICT utm_area FROM (SELECT area, best_utm(ST_Collect(area::geometry) OVER ()) AS z FROM users.stretches st JOIN users.stretch_countries stc ON stc.stretch_id = st.id WHERE country = (SELECT country FROM users.list_users WHERE username = current_user)) AS st; RETURN utm_area; END; $$ LANGUAGE plpgsql STABLE PARALLEL SAFE; CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger AS $$ BEGIN IF NEW.map_extent IS NULL THEN NEW.map_extent = ST_Extent(CAST(area AS geometry)) FROM users.stretches st JOIN users.stretch_countries stc ON stc.stretch_id = st.id WHERE stc.country = NEW.country; END IF; INSERT INTO internal.user_profiles ( username, country, map_extent, email_address) VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address); EXECUTE format( 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', NEW.username, NEW.rolname, internal.check_password(NEW.pw)); -- Do not leak new password NEW.pw = ''; RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER; -- -- search_functions: CREATE OR REPLACE FUNCTION search_stretches(search_string text) RETURNS jsonb LANGUAGE plpgsql STABLE PARALLEL SAFE AS $$ DECLARE _result jsonb; BEGIN SELECT COALESCE(json_agg(r),'[]') INTO _result FROM (SELECT id, objnam AS name, ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom, 'stretch' AS type FROM users.stretches WHERE objnam ILIKE '%' || search_string || '%' OR nobjnam ILIKE '%' || search_string || '%' ORDER BY name) r; RETURN _result; END; $$;