Created
March 8, 2016 18:57
-
-
Save lukasmartinelli/0317c926cf518fb3b264 to your computer and use it in GitHub Desktop.
Try optimize PostgreSQL function
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION point_to_tiles( | |
_point geometry | |
) RETURNS SETOF tile | |
AS $$ | |
DECLARE | |
d2r CONSTANT DOUBLE PRECISION := pi() / 180; | |
lon CONSTANT DOUBLE PRECISION := st_x(ST_Transform(_point, 4326)); | |
lat CONSTANT DOUBLE PRECISION := st_y(ST_Transform(_point, 4326)); | |
_sin CONSTANT DOUBLE PRECISION := sin(lat * d2r); | |
z1 CONSTANT DOUBLE PRECISION := pow(2, 1); | |
z2 CONSTANT DOUBLE PRECISION := pow(2, 2); | |
z3 CONSTANT DOUBLE PRECISION := pow(2, 3); | |
z4 CONSTANT DOUBLE PRECISION := pow(2, 4); | |
z5 CONSTANT DOUBLE PRECISION := pow(2, 5); | |
z6 CONSTANT DOUBLE PRECISION := pow(2, 6); | |
z7 CONSTANT DOUBLE PRECISION := pow(2, 7); | |
z8 CONSTANT DOUBLE PRECISION := pow(2, 8); | |
z9 CONSTANT DOUBLE PRECISION := pow(2, 9); | |
z10 CONSTANT DOUBLE PRECISION := pow(2, 10); | |
z11 CONSTANT DOUBLE PRECISION := pow(2, 11); | |
z12 CONSTANT DOUBLE PRECISION := pow(2, 12); | |
z13 CONSTANT DOUBLE PRECISION := pow(2, 13); | |
z14 CONSTANT DOUBLE PRECISION := pow(2, 14); | |
BEGIN | |
RETURN QUERY VALUES | |
(0, 0, 0), | |
(floor(z1 * (lon / 360 + 0.5)), floor(z1 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 1), | |
(floor(z2 * (lon / 360 + 0.5)), floor(z2 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 2), | |
(floor(z3 * (lon / 360 + 0.5)), floor(z3 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 3), | |
(floor(z4 * (lon / 360 + 0.5)), floor(z4 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 4), | |
(floor(z5 * (lon / 360 + 0.5)), floor(z5 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 5), | |
(floor(z6 * (lon / 360 + 0.5)), floor(z6 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 6), | |
(floor(z7 * (lon / 360 + 0.5)), floor(z7 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 7), | |
(floor(z8 * (lon / 360 + 0.5)), floor(z8 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 8), | |
(floor(z9 * (lon / 360 + 0.5)), floor(z9 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 9), | |
(floor(z10 * (lon / 360 + 0.5)), floor(z10 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 10), | |
(floor(z11 * (lon / 360 + 0.5)), floor(z10 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 11), | |
(floor(z12 * (lon / 360 + 0.5)), floor(z10 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 12), | |
(floor(z13 * (lon / 360 + 0.5)), floor(z10 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 13), | |
(floor(z14 * (lon / 360 + 0.5)), floor(z10 * (0.5 - 0.25 * log((1 + _sin) / (1 - _sin)) / pi())), 14); | |
END; | |
$$ LANGUAGE plpgsql IMMUTABLE; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment