Skip to content

Instantly share code, notes, and snippets.

@javisantana

javisantana/tile.sql

Created March 22, 2015 12:41
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save javisantana/2b12dcb66958ae0680ff to your computer and use it in GitHub Desktop.
Save javisantana/2b12dcb66958ae0680ff to your computer and use it in GitHub Desktop.
Download ZIP
Raw
tile.sql
CREATE OR REPLACE FUNCTION tile (z integer, x integer, y integer, query text) RETURNS TABLE(id int8, geom geometry)
AS $$
DECLARE
sql TEXT;
BEGIN
sql := 'with _conf as (
select
CDB_XYZ_resolution(' || z || ') as res,
1.0/CDB_XYZ_resolution(' || z || ') as invres,
st_xmin(CDB_XYZ_Extent(' || x || ',' || y || ',' || z ||')) as tile_x,
st_ymin(CDB_XYZ_Extent(' || x || ',' || y || ',' || z ||')) as tile_y
),
_geom as (
select ST_ClipByBox2d(
ST_Simplify(
ST_SnapToGrid(the_geom_webmercator, res/20, res/20),
res/20
),
CDB_XYZ_Extent(' || x || ',' || y || ',' || z ||')
) as _clip_geom, cartodb_id from (' || query || ') _wrap, _conf where the_geom_webmercator && CDB_XYZ_Extent(' || x || ',' || y || ',' || z ||')
)
select cartodb_id::int8 as id, ST_Affine(_clip_geom, invres, 0, 0, invres, -tile_x, -tile_y) as geom from _geom, _conf where not ST_IsEmpty(_clip_geom)
';
-- RAISE NOTICE 'sql: %', sql;
RETURN QUERY EXECUTE sql;
END;
$$ LANGUAGE plpgsql;
@ddierickx
Copy link

I'm sure you are aware but it is possible to create invalid geometries this way because of dimension collapses or created self-intersections (see ST_ClipByBox2d documentation).

@jwgmeligmeyling
Copy link

@ddierickx how could such be prevented?

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