On the fly size sort of polygons in CartoDB by andrewxhill

index.html

<html>
  <head>
    <meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
    <meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
    
    <style>
      html, body, #map {
        height: 100%;
        padding: 0;
        margin: 0;
      }
    </style>

    <link rel="stylesheet" href="//libs.cartocdn.com/cartodb.js/v2/themes/css/cartodb.css" />
    <!--[if lte IE 8]>
        <link rel="stylesheet" href="//libs.cartocdn.com/cartodb.js/v2/themes/css/cartodb.ie.css" />
    <![endif]-->

  </head>
    
  <body>
    <div id="map"></div>
  </body>


  <!-- include cartodb.js library -->
  <script src="//libs.cartocdn.com/cartodb.js/v2/cartodb.js"></script>

  <style type='text/css'>
    html, body {
      margin: 0;
      padding: 0;
      height: 100%;
      width: 100%;
      background: white;
    }
    #map{
      height: 100%;
      width: 100%;
      background: white;
    }
    #tooltip {
      position:absolute;
      right: 100px;
      width:140px;
      height:auto;
      padding:10px;
      z-index:1000;
      display:none;
      background:white;
      border-color:#DDDDDD!important;
      font:normal 12px Arial!important;
      color:#666666!important;
    }
    #tooltip label {font-weight:bold; width: 100%;}
  </style>
  <script>
    // starting latitude and longitude for our map
    var position = new L.LatLng(45.525302, -122.6701);
    
    // starting zoom
    var zoom = 16; 

    // is our Leaflet map object
    var map = new L.Map('map').setView(position, zoom)
      , mapboxUrl = '//{s}.tiles.mapbox.com/v3/cartodb.map-1nh578vv/{z}/{x}/{y}.png'
      //, mapboxUrl = '//tile.stamen.com/toner/{z}/{x}/{y}.jpg'
      , basemap = new L.TileLayer(mapboxUrl, {
        maxZoom: 20, 
        attribution: "CartoDB Tutorials"
        });

      cartodb.createLayer(map, '//osm2.cartodb.com/api/v1/viz/osm_export_polygon/viz.json', {
        query: "WITH RECURSIVE dims AS (SELECT 2*sqrt(sum(ST_Area(the_geom))) as d, sqrt(sum(ST_Area(the_geom)))/20 as w, count(*) as rows FROM osm_export_polygon WHERE the_geom IS NOT NULL),   geoms AS (SELECT the_geom, cartodb_id, ST_YMax(the_geom)-ST_YMin(the_geom) as height FROM osm_export_polygon WHERE the_geom IS NOT NULL ORDER BY ST_YMax(the_geom)-ST_YMin(the_geom)  DESC),  geomval AS (SELECT the_geom, cartodb_id, row_number() OVER (ORDER BY height DESC) as id from geoms),  positions(cartodb_id, the_geom,x_offset,y_offset,new_row,row_offset) AS (     (SELECT cartodb_id, the_geom, 0.0::float, 0.0::float, FALSE, 2 from geomval limit 1)    UNION ALL       (       SELECT        (SELECT cartodb_id FROM geomval WHERE id = p.row_offset),         (SELECT the_geom FROM geomval WHERE id = p.row_offset),           CASE WHEN             p.x_offset < s.d          THEN            (SELECT (s.w+(ST_XMax(the_geom) - ST_XMin(the_geom)))+p.x_offset FROM geomval WHERE id = p.row_offset)          ELSE            0           END as x_offset         ,           CASE WHEN             p.x_offset < s.d          THEN            p.y_offset          ELSE            (SELECT (s.w+(ST_YMax(the_geom) - ST_YMin(the_geom)))+p.y_offset FROM geomval WHERE id = p.row_offset)          END as y_offset         ,         FALSE,        p.row_offset+1      FROM          positions p, dims s       WHERE         p.row_offset < s.rows       )   ),  sfact AS (    SELECT ST_XMin(the_geom) as x, ST_YMax(the_geom) as y FROM geomval LIMIT 1  ) SELECT ST_Transform(ST_Translate(   the_geom,   (x - ST_XMin(the_geom) - x_offset),   (y - ST_YMin(the_geom) - y_offset)),3857) as the_geom_webmercator, cartodb_id FROM positions,sfact order by row_offset asc   ",
          tile_style: "#{{table_name}}{polygon-fill: red; line-color: #FFFFFF;}"})
       .on('done', function(layer) {
        map.addLayer(layer);

        layer.on('featureOver', function(e, pos, latlng, data) {
          cartodb.log.log(e, pos, latlng, data);
        });

        layer.on('error', function(err) {
          cartodb.log.log('error: ' + err);
        });

      }).on('error', function() {
        cartodb.log.log("some error occurred");
      });
  </script>
  </body>
</html>

osm_export_polygon.sql

WITH RECURSIVE 
    dims AS (
        SELECT 2*sqrt(sum(ST_Area(the_geom))) as d, sqrt(sum(ST_Area(the_geom)))/20 as w, count(*) as rows FROM osm_export_polygon WHERE the_geom IS NOT NULL),   
    geoms AS (
        SELECT the_geom, cartodb_id, ST_YMax(the_geom)-ST_YMin(the_geom) as height FROM osm_export_polygon WHERE the_geom IS NOT NULL ORDER BY ST_YMax(the_geom)-ST_YMin(the_geom)  DESC),  
    geomval AS (
        SELECT the_geom, cartodb_id, row_number() OVER (ORDER BY height DESC) as id from geoms),  
    positions(cartodb_id, the_geom,x_offset,y_offset,new_row,row_offset) AS (     
        (SELECT cartodb_id, the_geom, 0.0::float, 0.0::float, FALSE, 2 from geomval limit 1)    
        UNION ALL       
        (SELECT 
            (SELECT cartodb_id FROM geomval WHERE id = p.row_offset),
            (SELECT the_geom FROM geomval WHERE id = p.row_offset),
            CASE WHEN p.x_offset < s.d THEN (SELECT (s.w+(ST_XMax(the_geom) - ST_XMin(the_geom)))+p.x_offset FROM geomval WHERE id = p.row_offset) ELSE 0 END as x_offset,
            CASE WHEN p.x_offset < s.d THEN p.y_offset ELSE (SELECT (s.w+(ST_YMax(the_geom) - ST_YMin(the_geom)))+p.y_offset FROM geomval WHERE id = p.row_offset) END as y_offset , FALSE, p.row_offset+1 
        FROM positions p, dims s 
        WHERE p.row_offset < s.rows ) ),  
    sfact AS (    
        SELECT ST_XMin(the_geom) as x, ST_YMax(the_geom) as y FROM geomval LIMIT 1  ) 
SELECT 
    ST_Transform(ST_Translate( the_geom, (x - ST_XMin(the_geom) - x_offset), (y - ST_YMin(the_geom) - y_offset)),3857) as the_geom_webmercator, cartodb_id 
FROM positions,sfact 
order by row_offset asc