Mercurial > gemma

comparison schema/geo_functions.sql @ 3596:eeeb7bf14217

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
reverted best_utm() back to geography only version.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 04 Jun 2019 16:50:17 +0200
parents 033366f94abf
children
comparison
equal deleted inserted replaced
3595:d4ff8ea19f2c 3596:eeeb7bf14217
10 10
11 -- Author(s): 11 -- Author(s):
12 -- * Sascha L. Teichmann <sascha.teichmann@intevation.de> 12 -- * Sascha L. Teichmann <sascha.teichmann@intevation.de>
13 -- * Tom Gottfried <tom@intevation.de> 13 -- * Tom Gottfried <tom@intevation.de>
14 14
15 CREATE OR REPLACE FUNCTION best_utm(g geometry) RETURNS integer AS 15 CREATE OR REPLACE FUNCTION best_utm(g geography) RETURNS integer AS
16 $$ 16 $$
17 DECLARE 17 DECLARE
18 center geometry; 18 center geometry;
19 BEGIN 19 BEGIN
20 SELECT ST_Centroid(g) INTO center; 20 -- Centroid should be calculated on geography to get accurate results
21 -- from lon/lat coordinates, but the respective PostGIS function returns
22 -- POINT(-NaN NaN) for some invalid polygons, while the calculation on
23 -- geometry seems to give reasonable approximations in this context.
24 SELECT ST_Centroid(CAST(g AS geometry)) INTO center;
21 25
22 RETURN 26 RETURN
23 CASE WHEN ST_Y(center) > 0 THEN 27 CASE WHEN ST_Y(center) > 0 THEN
24 32600 28 32600
25 ELSE 29 ELSE
27 END + floor((ST_X(center)+180)/6)::int + 1; 31 END + floor((ST_X(center)+180)/6)::int + 1;
28 END; 32 END;
29 $$ 33 $$
30 LANGUAGE plpgsql 34 LANGUAGE plpgsql
31 IMMUTABLE; 35 IMMUTABLE;
32
33 CREATE OR REPLACE FUNCTION best_utm(g geography) RETURNS integer AS
34 $$
35 BEGIN
36 -- Centroid should be calculated on geography to get accurate results
37 -- from lon/lat coordinates, but the respective PostGIS function returns
38 -- POINT(-NaN NaN) for some invalid polygons, while the calculation on
39 -- geometry seems to give reasonable approximations in this context.
40 RETURN best_utm(CAST(g as geometry));
41 END;
42 $$
43 LANGUAGE plpgsql
44 IMMUTABLE;
45