Mercurial > gemma

annotate schema/geo_functions.sql @ 2455:54c9fe587fe6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Subdivide SQL function to prepare for improved error handling The context of an error (e.g. the function in which it occured) can be inferred by the database client. Not doing all in one statement will render the context more meaningful.
author Tom Gottfried <tom@intevation.de>
date Fri, 01 Mar 2019 18:38:02 +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;