Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
CREATE OR REPLACE FUNCTION lon2tile(lon DOUBLE PRECISION, zoom INTEGER)
RETURNS INTEGER AS
$BODY$
SELECT FLOOR( (lon + 180) / 360 * (1 << zoom) )::INTEGER;
$BODY$
LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION lat2tile(lat double precision, zoom integer)
RETURNS integer AS
$BODY$
SELECT floor( (1.0 - ln(tan(radians(lat)) + 1.0 / cos(radians(lat))) / pi()) / 2.0 * (1 << zoom) )::integer;
$BODY$
LANGUAGE sql IMMUTABLE;
CREATE OR REPLACE FUNCTION tile2lat(y integer, zoom integer)
RETURNS double precision AS
$BODY$
DECLARE
n float;
sinh float;
E float = 2.7182818284;
BEGIN
n = pi() - (2.0 * pi() * y) / power(2.0, zoom);
sinh = (1 - power(E, -2*n)) / (2 * power(E, -n));
return degrees(atan(sinh));
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTION tile2lon(x integer, zoom integer)
RETURNS double precision AS
$BODY$
SELECT CAST(x * 1.0 / (1 << zoom) * 360.0 - 180.0 AS double precision);
$BODY$
LANGUAGE sql IMMUTABLE;
CREATE OR REPLACE FUNCTION getTileEnvelope(geom geometry, zoom integer)
RETURNS geometry AS
$BODY$
DECLARE
_result geometry;
BEGIN
IF ST_SRID(geom) != 4326 THEN
geom = ST_Transform(geom, 4326);
END IF;
SELECT ST_MakeEnvelope(bbox.xmin, bbox.ymin, bbox.xmax, bbox.ymax, 4326) INTO _result FROM (
SELECT tile2lon(tile.x, zoom) AS xmin, tile2lat(y, zoom) AS ymin, tile2lon(tile.x + 1, zoom) AS xmax, tile2lat(y + 1, zoom) AS ymax FROM (
SELECT lon2tile(ST_X(geom), zoom) AS x, lat2tile(ST_Y(geom), zoom) AS y
) AS tile
) AS bbox;
RETURN _result;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTION getTileEnvelope(geog geography, zoom integer)
RETURNS geography AS
$BODY$
DECLARE
_result geography;
BEGIN
SELECT ST_MakeEnvelope(bbox.xmin, bbox.ymin, bbox.xmax, bbox.ymax, 4326)::geography INTO _result FROM (
SELECT tile2lon(tile.x, zoom) AS xmin, tile2lat(y, zoom) AS ymin, tile2lon(tile.x + 1, zoom) AS xmax, tile2lat(y + 1, zoom) AS ymax FROM (
SELECT lon2tile(ST_X(geog::geometry), zoom) AS x, lat2tile(ST_Y(geog::geometry), zoom) AS y
) AS tile
) AS bbox;
RETURN _result;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;