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;