Mercurial > gemma

annotate schema/geo_functions.sql @ 3548:f3102fa16a69

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
client: data availability/accuracy: inject mapId into layer/styles factory to fix display issue with two maps This layer displays features based on which other layers are visible. To detect those other layers visibility realiably, the layer needs to be aware about the map it belongs to.
author Markus Kottlaender <markus@intevation.de>
date Fri, 31 May 2019 12:45:30 +0200
parents 69292eb68984
children 033366f94abf
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
2833
1b6840093eac Prevent calculation of wrong UTM zones
Tom Gottfried <tom@intevation.de>
parents: 1832
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
2838
522ed5eb449c Work-around stability issue of ST_Centroid(geography)
Tom Gottfried <tom@intevation.de>
parents: 2833
diff changeset
20 -- Centroid should be calculated on geography to get accurate results
522ed5eb449c Work-around stability issue of ST_Centroid(geography)
Tom Gottfried <tom@intevation.de>
parents: 2833
diff changeset
21 -- from lon/lat coordinates, but the respective PostGIS function returns
522ed5eb449c Work-around stability issue of ST_Centroid(geography)
Tom Gottfried <tom@intevation.de>
parents: 2833
diff changeset
22 -- POINT(-NaN NaN) for some invalid polygons, while the calculation on
522ed5eb449c Work-around stability issue of ST_Centroid(geography)
Tom Gottfried <tom@intevation.de>
parents: 2833
diff changeset
23 -- geometry seems to give reasonable approximations in this context.
522ed5eb449c Work-around stability issue of ST_Centroid(geography)
Tom Gottfried <tom@intevation.de>
parents: 2833
diff changeset
24 SELECT ST_Centroid(CAST(g AS geometry)) INTO center;
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;