Mercurial > gemma

annotate schema/geo_functions.sql @ 5684:536e842d9bfa sr-v2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Reorder vertices in tins to minimize delta distances.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Sun, 11 Feb 2024 22:32:55 +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;