Mercurial > gemma

annotate schema/geo_functions.sql @ 3589:033366f94abf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added a geometry version of best_utm() for convenience.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 04 Jun 2019 12:29:20 +0200
parents 69292eb68984
children eeeb7bf14217
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
2833
1b6840093eac Prevent calculation of wrong UTM zones
Tom Gottfried <tom@intevation.de>
parents: 1832
diff changeset
7 -- Copyright (C) 2018, 2019 by via donau
1832
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>
2833
1b6840093eac Prevent calculation of wrong UTM zones
Tom Gottfried <tom@intevation.de>
parents: 1832
diff changeset
13 -- * Tom Gottfried <tom@intevation.de>
1832
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14
3589
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
15 CREATE OR REPLACE FUNCTION best_utm(g geometry) RETURNS integer AS
1832
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 $$
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 DECLARE
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 center geometry;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 BEGIN
3589
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
20 SELECT ST_Centroid(g) INTO center;
2838
522ed5eb449c Work-around stability issue of ST_Centroid(geography)
Tom Gottfried <tom@intevation.de>
parents: 2833
diff changeset
21
1832
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 RETURN
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 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
24 32600
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 ELSE
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 32700
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 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
28 END;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 $$
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 LANGUAGE plpgsql
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 IMMUTABLE;
3589
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
32
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
33 CREATE OR REPLACE FUNCTION best_utm(g geography) RETURNS integer AS
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
34 $$
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
35 BEGIN
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
36 -- Centroid should be calculated on geography to get accurate results
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
37 -- from lon/lat coordinates, but the respective PostGIS function returns
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
38 -- POINT(-NaN NaN) for some invalid polygons, while the calculation on
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
39 -- geometry seems to give reasonable approximations in this context.
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
40 RETURN best_utm(CAST(g as geometry));
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
41 END;
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
42 $$
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
43 LANGUAGE plpgsql
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
44 IMMUTABLE;
033366f94abf Added a geometry version of best_utm() for convenience.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2915
diff changeset
45