Mercurial > gemma
annotate schema/geo_functions.sql @ 2015:b4ba751e70a1
Fix column type to match referenced column
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Tue, 29 Jan 2019 12:36:45 +0100 |
parents | 661597546ed9 |
children | 1b6840093eac |
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; |