Tiles size calculations

gistfile1.txt

breakdown of requests by zoom

vector=# select zoom, count(zoom), min(size), max(size), round(avg(size)) as mean 
from traffic group by zoom having zoom > 0 order by zoom;

 zoom | count  | min |   max   | mean  
------+--------+-----+---------+-------
    1 |  10801 |  29 |   10218 |   956
    2 |  19237 |  29 |    7862 |  1155
    3 |  25753 |   8 |  182731 |  3484
    4 |  33656 |  29 |  280731 |  9799
    5 |  38634 |  29 |   86402 |  5535
    6 |  45464 |  29 |   87874 |  3910
    7 |  55222 |  29 |  297267 | 22570
    8 |  61231 |  29 |  574925 |  8919
    9 |  77390 |   8 |  277763 | 29322
   10 | 156610 |   0 | 1610283 | 42227
   11 | 425129 |   0 |  935336 | 37569
   12 | 183400 |   0 | 1424389 | 17153
   13 | 145541 |  42 |  644204 | 16778
   14 | 213070 |   0 | 2384836 | 26080
   15 | 209245 |   0 |  796694 | 16686
   16 | 286342 |   0 |  368958 |  8455
   17 | 199608 |   0 |  141437 |  5607
   18 | 109554 |  38 |   33675 |  1862
   19 |  44485 |  42 |   19233 |   987
   20 |  27084 |  92 |   19161 |   628


Average tile size by zoom

 > topojson

vector=# select zoom, count(zoom), min(size), max(size), round(avg(size)) as mean 
from traffic where format = 'topojson' group by zoom having zoom > 0 order by zoom;
 zoom | count  | min  |  max   | mean  
------+--------+------+--------+-------
   10 |  69268 |  698 | 487083 | 62228
   11 | 318040 |  184 | 707634 | 44085
   12 |   3217 |  632 | 763731 | 39784
   13 |   1215 |  294 | 331767 | 16679
   14 |    377 | 4269 | 164943 | 56239
   15 |    143 |  614 |  51901 | 25609
   16 |   1366 |  176 |  24465 |  6647
   17 |    299 |  326 |  38740 |  5835
   18 |    323 |  326 |   4991 |  2136
   19 |    196 |  356 |   2380 |   944
   20 |     24 |  545 |   1353 |   756

 > vtm

vector=# select zoom, count(zoom), min(size), max(size), round(avg(size)) as mean 
from traffic where format = 'vtm' group by zoom having zoom > 0 order by zoom;
 zoom | count | min |  max   | mean  
------+-------+-----+--------+-------
    2 |  5656 | 150 |   7862 |  1762
    3 |  5839 |   8 |  28028 |  5625
    4 |  6041 | 110 |  82352 | 17265
    5 |  8618 | 143 |  36409 |  7025
    6 |  8233 | 143 |  25652 |  3877
    7 |  9323 | 143 | 282061 |  8622
    8 | 11452 | 110 | 145763 |  2990
    9 | 16547 |   8 | 207608 |  8118
   10 | 22755 |   0 | 307232 |  6855
   11 | 40696 |   0 | 232132 |  4955
   12 | 70721 |   0 | 353824 |  6921
   13 | 45782 | 110 | 388831 |  6552
   14 | 73422 |   0 | 379507 |  4024
   15 | 30407 | 110 | 100969 |  3089
   16 | 31572 |   0 |  52488 |  2408
   17 | 27953 |   0 |  29958 |  1069
   18 |    24 | 347 |    863 |   567

 > json

vector=# select zoom, count(zoom), min(size), max(size), round(avg(size)) as mean 
from traffic where format = 'json' group by zoom having zoom > 0 order by zoom;

 zoom | count | min |   max   | mean  
------+-------+-----+---------+-------
    1 |   130 |  42 |   10218 |   789
    2 |   201 |  58 |    7075 |   814
    3 |   212 |  58 |  182731 |  8239
    4 |   728 |  58 |  280731 |  5827
    5 |   274 |  58 |   86402 |  5610
    6 |   532 |  42 |   87874 |  5241
    7 |   845 |  58 |  297267 | 22401
    8 |   898 |  42 |  574925 | 11098
    9 |  1047 |  42 |  277763 | 43450
   10 |  1665 |  42 | 1610283 | 40936
   11 |  1152 |  42 |  935336 | 30778
   12 | 37740 |  42 | 1424389 |  3062
   13 |  5364 |  42 |  644204 | 27199
   14 | 18377 |  42 | 2384836 | 91279
   15 | 41067 |  42 |  796694 | 36678
   16 | 33640 |  42 |  368958 | 19047
   17 | 40240 |  42 |  141437 | 14661
   18 | 11149 |  42 |   33675 |  2429
   19 |  4282 |  42 |    6053 |   697
   20 |  1213 | 223 |    2823 |   733

 > mapbox

vector=# select zoom, count(zoom), min(size), max(size), round(avg(size)) as mean 
from traffic where format = 'mapbox' group by zoom having zoom > 0 order by zoom;
 zoom | count  | min |  max   | mean  
------+--------+-----+--------+-------
    1 |  10670 |  29 |   8321 |   958
    2 |  13380 |  29 |   6066 |   904
    3 |  19702 |  29 |  19367 |  2798
    4 |  26887 |  29 |  61098 |  8229
    5 |  29742 |  29 |  64424 |  5103
    6 |  36679 |  29 |  67653 |  3900
    7 |  45054 |  29 | 281267 | 25460
    8 |  48881 |  29 | 122186 | 10268
    9 |  59738 |  29 | 191760 | 34976
   10 |  62747 |  29 | 406133 | 33118
   11 |  65241 |   0 | 247147 | 26272
   12 |  71698 |  29 | 358922 | 33653
   13 |  93095 |  92 | 178381 | 21223
   14 | 120854 |  34 | 233878 | 29326
   15 | 137604 |   0 | 119981 | 13657
   16 | 219755 |   0 | 108065 |  7714
   17 | 131116 |   0 |  45789 |  3795
   18 |  98058 |  38 |  23360 |  1797
   19 |  40007 |  92 |  19233 |  1019
   20 |  25847 |  92 |  19161 |   623

Weighted means by format

 > mapbox
vector=# select round(weighted_mean(count, cast(mean as int)), 2) 
from aggregate_by_zoom where format = 'mapbox';
  round   
----------
 76897.24

 > json
vector=# select round(weighted_mean(count, cast(mean as int)), 2) 
from aggregate_by_zoom where format = 'json';
  round   
----------
 13196.24

 > topojson
vector=# select round(weighted_mean(count, cast(mean as int)), 2) 
from aggregate_by_zoom where format = 'topojson';
  round   
----------
 70958.43

 > vtm
vector=# select round(weighted_mean(count, cast(mean as int)), 2) 
from aggregate_by_zoom where format = 'vtm';
  round   
----------
 23231.41