Skip to content

Instantly share code, notes, and snippets.

@lukasmartinelli
Created March 8, 2016 18:57
Show Gist options
  • Save lukasmartinelli/0317c926cf518fb3b264 to your computer and use it in GitHub Desktop.
Save lukasmartinelli/0317c926cf518fb3b264 to your computer and use it in GitHub Desktop.
Try optimize PostgreSQL function
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