Mercurial > gemma

annotate schema/geo_functions.sql @ 2006:35acb7f9ae0c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Do anything else before expectedly failing role creation Creating roles during database setup expectedly fails in case there already is another gemma database in the cluster. Doing it at the end of the transaction ensures it does not hide errors in other commands in the script. In passing, add the default admin via the designated view to ensure it will become a correctly set up application user.
author Tom Gottfried <tom@intevation.de>
date Thu, 24 Jan 2019 17:23:43 +0100
parents 661597546ed9
children 1b6840093eac
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1832
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 -- without warranty, see README.md and license for details.
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 -- Copyright (C) 2018 by via donau
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 -- Software engineering by Intevation GmbH
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 -- Author(s):
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 -- * Sascha L. Teichmann <sascha.teichmann@intevation.de>
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 CREATE OR REPLACE FUNCTION best_utm(g geometry) RETURNS integer AS
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 $$
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 DECLARE
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 center geometry;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 BEGIN
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 SELECT ST_Centroid(g) INTO center;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 RETURN
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 CASE WHEN ST_Y(center) > 0 THEN
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 32600
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 ELSE
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 32700
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 END + floor((ST_X(center)+180)/6)::int + 1;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 END;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 $$
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 LANGUAGE plpgsql
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 IMMUTABLE;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 CREATE OR REPLACE FUNCTION utm_covers(g geography) RETURNS boolean AS
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 $$
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 DECLARE
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 user_area geometry;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 utm integer;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 BEGIN
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 SELECT area::geometry FROM users.responsibility_areas INTO user_area
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 WHERE country = users.current_user_country();
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 SELECT best_utm(user_area) INTO utm;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 RETURN ST_Covers(
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 ST_Transform(user_area, utm),
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 ST_Transform(g::geometry, utm));
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 END;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 $$
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 LANGUAGE plpgsql
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 STABLE;