Mercurial > gemma

annotate schema/geo_functions.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
parents eeeb7bf14217
children
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
3596
eeeb7bf14217 reverted best_utm() back to geography only version.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3589
diff changeset
15 CREATE OR REPLACE FUNCTION best_utm(g geography) 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
3596
eeeb7bf14217 reverted best_utm() back to geography only version.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3589
diff changeset
20 -- Centroid should be calculated on geography to get accurate results
eeeb7bf14217 reverted best_utm() back to geography only version.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3589
diff changeset
21 -- from lon/lat coordinates, but the respective PostGIS function returns
eeeb7bf14217 reverted best_utm() back to geography only version.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3589
diff changeset
22 -- POINT(-NaN NaN) for some invalid polygons, while the calculation on
eeeb7bf14217 reverted best_utm() back to geography only version.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3589
diff changeset
23 -- geometry seems to give reasonable approximations in this context.
eeeb7bf14217 reverted best_utm() back to geography only version.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3589
diff changeset
24 SELECT ST_Centroid(CAST(g AS geometry)) INTO center;
2838
522ed5eb449c Work-around stability issue of ST_Centroid(geography)
Tom Gottfried <tom@intevation.de>
parents: 2833
diff changeset
25
1832
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 RETURN
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 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
28 32600
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 ELSE
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 32700
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 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
32 END;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 $$
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 LANGUAGE plpgsql
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 IMMUTABLE;