Skip to content

Instantly share code, notes, and snippets.

@jatorre
Created December 15, 2010 15:06
Show Gist options
  • Save jatorre/742047 to your computer and use it in GitHub Desktop.
Save jatorre/742047 to your computer and use it in GitHub Desktop.
A function to create a PostGIS geometry out of a Google Tile X/Y/Z definition
-- Function: v_get_tile(integer, integer, integer)
-- DROP FUNCTION v_get_tile(integer, integer, integer);
CREATE OR REPLACE FUNCTION v_get_tile(x integer, y integer, z integer)
RETURNS geometry AS
$BODY$
DECLARE
origin_shift CONSTANT FLOAT := 20037508.342789244;
initial_resolution CONSTANT FLOAT := 156543.03392804062;
res float;
minx float;
miny float;
maxx float;
maxy float;
BEGIN
res := initial_resolution / (power(2,z));
minx := (x*256)*res - origin_shift;
miny := -((y*256)*res - origin_shift);
maxx := ((x+1)*256)*res - origin_shift;
maxy := -(((y+1)*256)*res - origin_shift);
RETURN (ST_GeomFromText('POLYGON(('||minx||' '||maxy||','||maxx||' '||maxy||','||maxx||' '||miny||','||minx||' '||miny||','||minx||' '||maxy||'))',900913));END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE STRICT
COST 100;
ALTER FUNCTION v_get_tile(integer, integer, integer) OWNER TO postgres;
@javisantana
Copy link

this will be very useful for our vector tile server!

@jatorre
Copy link
Author

jatorre commented Dec 14, 2011

True. Didnt realize you where doing all the coding for this. We should include this functions in all CartoDB

@javisantana
Copy link

we are doing it on the client side and asking for the geometry inside the bbox

@jatorre
Copy link
Author

jatorre commented Dec 14, 2011

By the way, here is a method to generate all tiles for a specific zoom

SELECT ST_Transform(v_get_tile(x,y,2),3857) as the_geom_webmercator from(
select distinct * from generate_series(0,3) as x, generate_series(0,3) as y) as l

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment