Mercurial > gemma
changeset 1832:661597546ed9
Move not only authentication related functions to own file
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Wed, 16 Jan 2019 17:30:13 +0100 |
parents | 74a3d8d8939e |
children | b9c59050014a |
files | schema/auth.sql schema/geo_functions.sql schema/install-db.sh |
diffstat | 3 files changed, 47 insertions(+), 34 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/auth.sql Wed Jan 16 17:20:19 2019 +0100 +++ b/schema/auth.sql Wed Jan 16 17:30:13 2019 +0100 @@ -105,40 +105,6 @@ -- RLS policies for waterway_admin -- -CREATE OR REPLACE FUNCTION best_utm(g geometry) RETURNS integer AS -$$ -DECLARE - center geometry; -BEGIN - SELECT ST_Centroid(g) INTO center; - RETURN - CASE WHEN ST_Y(center) > 0 THEN - 32600 - ELSE - 32700 - END + floor((ST_X(center)+180)/6)::int + 1; -END; -$$ -LANGUAGE plpgsql -IMMUTABLE; - -CREATE OR REPLACE FUNCTION utm_covers(g geography) RETURNS boolean AS -$$ -DECLARE - user_area geometry; - utm integer; -BEGIN - SELECT area::geometry FROM users.responsibility_areas INTO user_area - WHERE country = users.current_user_country(); - SELECT best_utm(user_area) INTO utm; - RETURN ST_Covers( - ST_Transform(user_area, utm), - ST_Transform(g::geometry, utm)); -END; -$$ -LANGUAGE plpgsql -STABLE; - -- Staging area -- TODO: add all relevant tables here
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/geo_functions.sql Wed Jan 16 17:30:13 2019 +0100 @@ -0,0 +1,46 @@ +-- 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) 2018 by via donau +-- – Österreichische Wasserstraßen-Gesellschaft mbH +-- Software engineering by Intevation GmbH + +-- Author(s): +-- * Sascha L. Teichmann <sascha.teichmann@intevation.de> + +CREATE OR REPLACE FUNCTION best_utm(g geometry) RETURNS integer AS +$$ +DECLARE + center geometry; +BEGIN + SELECT ST_Centroid(g) INTO center; + RETURN + CASE WHEN ST_Y(center) > 0 THEN + 32600 + ELSE + 32700 + END + floor((ST_X(center)+180)/6)::int + 1; +END; +$$ +LANGUAGE plpgsql +IMMUTABLE; + +CREATE OR REPLACE FUNCTION utm_covers(g geography) RETURNS boolean AS +$$ +DECLARE + user_area geometry; + utm integer; +BEGIN + SELECT area::geometry FROM users.responsibility_areas INTO user_area + WHERE country = users.current_user_country(); + SELECT best_utm(user_area) INTO utm; + RETURN ST_Covers( + ST_Transform(user_area, utm), + ST_Transform(g::geometry, utm)); +END; +$$ +LANGUAGE plpgsql +STABLE;
--- a/schema/install-db.sh Wed Jan 16 17:20:19 2019 +0100 +++ b/schema/install-db.sh Wed Jan 16 17:30:13 2019 +0100 @@ -121,6 +121,7 @@ -c "SET client_min_messages TO WARNING;" \ -f "$BASEDIR/isrs.sql" \ -f "$BASEDIR/gemma.sql" \ + -f "$BASEDIR/geo_functions.sql" \ -f "$BASEDIR/search.sql" \ -f "$BASEDIR/geonames.sql" \ -f "$BASEDIR/manage_users.sql" \