Mercurial > gemma

comparison schema/geo_functions.sql @ 1832:661597546ed9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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;