Mercurial > gemma
comparison schema/geo_functions.sql @ 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 | |
children | 1b6840093eac |
comparison
equal
deleted
inserted
replaced
1831:74a3d8d8939e | 1832:661597546ed9 |
---|---|
1 -- This is Free Software under GNU Affero General Public License v >= 3.0 | |
2 -- without warranty, see README.md and license for details. | |
3 | |
4 -- SPDX-License-Identifier: AGPL-3.0-or-later | |
5 -- License-Filename: LICENSES/AGPL-3.0.txt | |
6 | |
7 -- Copyright (C) 2018 by via donau | |
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH | |
9 -- Software engineering by Intevation GmbH | |
10 | |
11 -- Author(s): | |
12 -- * Sascha L. Teichmann <sascha.teichmann@intevation.de> | |
13 | |
14 CREATE OR REPLACE FUNCTION best_utm(g geometry) RETURNS integer AS | |
15 $$ | |
16 DECLARE | |
17 center geometry; | |
18 BEGIN | |
19 SELECT ST_Centroid(g) INTO center; | |
20 RETURN | |
21 CASE WHEN ST_Y(center) > 0 THEN | |
22 32600 | |
23 ELSE | |
24 32700 | |
25 END + floor((ST_X(center)+180)/6)::int + 1; | |
26 END; | |
27 $$ | |
28 LANGUAGE plpgsql | |
29 IMMUTABLE; | |
30 | |
31 CREATE OR REPLACE FUNCTION utm_covers(g geography) RETURNS boolean AS | |
32 $$ | |
33 DECLARE | |
34 user_area geometry; | |
35 utm integer; | |
36 BEGIN | |
37 SELECT area::geometry FROM users.responsibility_areas INTO user_area | |
38 WHERE country = users.current_user_country(); | |
39 SELECT best_utm(user_area) INTO utm; | |
40 RETURN ST_Covers( | |
41 ST_Transform(user_area, utm), | |
42 ST_Transform(g::geometry, utm)); | |
43 END; | |
44 $$ | |
45 LANGUAGE plpgsql | |
46 STABLE; |