Torque Tiles in SQL Speed tests

torquetile_speed.txt

Manhatten east of Lincoln Tunnel
14, 4824, 6157

----------------------------------------------------------------------
Tile creation:

SELECT Count(*) from (
SELECT Count(1) AS count
,floor(256*(((2^14)*(20037508 + ST_X(the_geom_webmercator))/40075016)::numeric % 1)) AS x
,floor(256*(((2^14)*(20037508 + ST_Y(the_geom_webmercator))/40075016)::numeric % 1)) AS y  
FROM taxis_256
WHERE quadkey LIKE xyz2quadkey(4824, 6157, 14) || '%'
GROUP BY x, y
) as q;

  taxis_256, 11ms, 267 pixels
  taxis_64, 23ms, 938 pixels
  taxis_16, 66ms, 2857 pixels
  taxis_4, 245ms, 6870 pixels
  taxis_1, 1537ms, 14036 pixels


----------------------------------------------------------------------
Record gathering:
select count(*) from taxis_1 where quadkey LIKE xyz2quadkey(4824, 6157, 14) || '%';

  taxis_256, 1ms, 843 records
  taxis_64, 4ms, 3474 records
  taxis_16, 14ms, 13994 records
  taxis_4, 59ms, 55254 records
  taxis_1, 750ms, 220715 records


----------------------------------------------------------------------
Faster Tile Creation

EXPLAIN SELECT Count(1) FROM (
SELECT Count(1) AS count,
floor(scale*(st_x(the_geom_webmercator)-x_origin)) AS x,
floor(scale*(st_y(the_geom_webmercator)-y_origin)) AS y
FROM taxis_1, 
  (SELECT
  (2^14) * 256 / 40075016.6855784 AS scale,
  20037508.3427892 - (6157+1) * 40075016.6855784/(2^14) AS y_origin,
  4824 * 40075016.6855784/(2^14) - 20037508.3427892 AS x_origin) AS const
WHERE quadkey LIKE xyz2quadkey(4824, 6157, 14) || '%'
GROUP BY x, y
) AS q;

  taxis_256, 4ms, 267 pixels
  taxis_64, 7ms, 935 pixels
  taxis_16, 24ms, 2859 pixels
  taxis_4, 97ms, 6867 pixels
  taxis_1, 917ms, 14044 pixels


----------------------------------------------------------------------
Faster Tile Creation without PostGIS

SELECT Count(1) FROM (
SELECT Count(1) AS count,
floor(const.scale*(x-x_origin)) AS mx,
floor(const.scale*(y-y_origin)) AS my
FROM ftaxis_4, 
  (SELECT
  (2^14) * 256 / 40075016.6855784 AS scale,
  20037508.3427892 - (6157+1) * 40075016.6855784/(2^14) AS y_origin,
  4824 * 40075016.6855784/(2^14) - 20037508.3427892 AS x_origin) AS const
WHERE quadkey LIKE xyz2quadkey(4824, 6157, 14) || '%'
GROUP BY mx, my
) AS q;