Mercurial > gemma
annotate schema/geo_functions.sql @ 5677:a57be8bfb6ea sr-v2
More suited file names.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Sun, 11 Feb 2024 09:51:21 +0100 |
parents | eeeb7bf14217 |
children |
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; |