raster_tiles.pgb

-- File to launch raster tiles request with different zoom levels
-- Run with something like:
-- pgbench -c 4 -T 30 -f raster_tiles.pgb -U postgres cartodb_dev_user_3e4a6fc6-4137-4c59-bc63-066f80efb90e_db -DZOOM_CURRENT=1 -DTABLE="yellow_1m" -DTABLE_MAP_MIN_X=77092 -DTABLE_MAP_MAX_X=77341 -DTABLE_MAP_MIN_Y=98328 -DTABLE_MAP_MAX_Y=98674

-----////////////////////// SCRIPT //////////////////////-----
----- Zoom levels
\set ZOOM_MAX 18
\set ZOOM_INV :ZOOM_MAX - :ZOOM_CURRENT

--- Helpers
----- pow() isnt currently supported, so we use and external command here
\setshell POWER2  awk 'BEGIN {p=2^ARGV[1]; print p }' :ZOOM_CURRENT
\set POWER2_INV   262144 / :POWER2
\set SNAP 0.0149291 * :POWER2_INV
\set SIMPLIFY :SNAP * 2

--- TILE
\set EARTH_RADIUS 6378137
\set EARTH_DIAMETER :EARTH_RADIUS * 2
\set EARTH_CIRCUMFERENCE :EARTH_DIAMETER * pi()
\set MAX_RES :EARTH_CIRCUMFERENCE / 256
\set ORIGIN_SHIFT :EARTH_CIRCUMFERENCE / 2

\set BASE_TILE_X random(:TABLE_MAP_MIN_X, :TABLE_MAP_MAX_X)
\set BASE_TILE_Y random(:TABLE_MAP_MIN_Y, :TABLE_MAP_MAX_Y)

-- Look for the tile at zoom level ZOOM_CURRENT that contains this tile
\set TILE_X :BASE_TILE_X / :POWER2_INV
\set TILE_Y :BASE_TILE_Y / :POWER2_INV

\set RESOLUTION :MAX_RES / :POWER2
\set BBOX_MIN_X (:TILE_X * 256 * :RESOLUTION) - :ORIGIN_SHIFT
\set BBOX_MAX_X ((:TILE_X + 1) * 256 * :RESOLUTION) - :ORIGIN_SHIFT
\set BBOX_MIN_Y - ((:TILE_Y + 1) * 256 * :RESOLUTION - :ORIGIN_SHIFT)
\set BBOX_MAX_Y - ((:TILE_Y) * 256 * :RESOLUTION - :ORIGIN_SHIFT)


SELECT ST_AsBinary(ST_Simplify(ST_SnapToGrid("the_geom_webmercator",:SNAP), :SIMPLIFY)) AS geom FROM
    (SELECT * FROM :TABLE) as cdbq
WHERE "the_geom_webmercator" && ST_SetSRID('BOX3D(:BBOX_MIN_X :BBOX_MIN_Y , :BBOX_MAX_X :BBOX_MAX_Y)'::box3d, 3857);