Mercurial > gemma

annotate schema/geo_functions.sql @ 2838:522ed5eb449c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Work-around stability issue of ST_Centroid(geography)
author Tom Gottfried <tom@intevation.de>
date Wed, 27 Mar 2019 19:15:25 +0100
parents 1b6840093eac
children 69292eb68984
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;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 CREATE OR REPLACE FUNCTION utm_covers(g geography) RETURNS boolean AS
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 $$
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 DECLARE
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 user_area geometry;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 utm integer;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 BEGIN
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 SELECT area::geometry FROM users.responsibility_areas INTO user_area
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 WHERE country = users.current_user_country();
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 SELECT best_utm(user_area) INTO utm;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 RETURN ST_Covers(
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 ST_Transform(user_area, utm),
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 ST_Transform(g::geometry, utm));
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 END;
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 $$
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 LANGUAGE plpgsql
661597546ed9 Move not only authentication related functions to own file
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 STABLE;