Last active
July 19, 2017 09:00
-
-
Save ernesmb/6b6ed27230c22c8c58bcc18c713ac024 to your computer and use it in GitHub Desktop.
pixel hexbin aggregation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <title>Aggregated content from clustered features II - Average Population| CartoDB</title> | |
| <meta name="viewport" content="initial-scale=1.0, user-scalable=no" /> | |
| <meta http-equiv="content-type" content="text/html; charset=UTF-8"/> | |
| <link rel="shortcut icon" href="http://cartodb.com/assets/favicon.ico" /> | |
| <style> | |
| html, body, #map { | |
| height: 100%; | |
| padding: 0; | |
| margin: 0; | |
| } | |
| </style> | |
| <link rel="stylesheet" href="https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/themes/css/cartodb.css" /> | |
| </head> | |
| <body> | |
| <div id="map"></div> | |
| <!-- include cartodb.js library --> | |
| <script src="https://cartodb-libs.global.ssl.fastly.net/cartodb.js/v3/3.15/cartodb.js"></script> | |
| <!-- Drop your code between the script tags below! --> | |
| <script type="infowindow/html" id="infowindow_template"> | |
| <div class="cartodb-popup"> | |
| <a href="#close" class="cartodb-popup-close-button close">x</a> | |
| <div class="cartodb-popup-content-wrapper"> | |
| <div class="cartodb-popup-content"> | |
| <!-- content.data contains the field info --> | |
| <h4>Features Count</h4> | |
| <p>{{content.data.points_count}}</p> | |
| <h4>Average Population</h4> | |
| <p>{{content.data.average}}</p> | |
| </div> | |
| </div> | |
| <div class="cartodb-popup-tip-container"></div> | |
| </div> | |
| </script> | |
| <script type="sql/html" id="sql_template_a"> | |
| WITH | |
| hgrid AS ( | |
| SELECT | |
| CDB_HexagonGrid( | |
| ST_Expand( | |
| !bbox!, | |
| greatest(!pixel_width!,!pixel_height!) * 24 | |
| ), greatest(!pixel_width!,!pixel_height!) * 24 | |
| ) AS cell | |
| ) | |
| SELECT | |
| hgrid.cell AS the_geom_webmercator, | |
| count(i.cartodb_id) AS points_count, | |
| row_number() over () AS cartodb_id, | |
| array_agg(cartodb_id) AS id_list, | |
| avg(pop_max) AS average | |
| FROM | |
| hgrid, | |
| (select * from ne_10m_populated_places_simple_7_copy) i | |
| WHERE | |
| ST_Intersects( | |
| i.the_geom_webmercator, | |
| hgrid.cell | |
| ) | |
| GROUP BY hgrid.cell | |
| </script> | |
| <style type="text/cartocss" id="cartoCSS"> | |
| #layer{ | |
| line-width:1px; | |
| line-color:#333; | |
| line-opacity:0.5; | |
| [ average <= 35676000] { polygon-fill: #BD0026;} | |
| [ average <= 300424] { polygon-fill: #F03B20;} | |
| [ average <= 111461] { polygon-fill: #FD8D3C;} | |
| [ average <= 47653] { polygon-fill: #FECC5C;} | |
| [ average <= 15601] { polygon-fill: #FFFFB2;} | |
| } | |
| </style> | |
| <script> | |
| var map=L.map('map', {center: [36,0],zoom:3}); | |
| function main(){ | |
| L.tileLayer('http://{s}.tile.stamen.com/toner/{z}/{x}/{y}.png', { | |
| attribution:'Stamen and CartoDB attribution' | |
| }).addTo(map); | |
| var clusterSQL = $('#sql_template_a').html(); | |
| var cartoCSS = $('#cartoCSS').html(); | |
| cartodb.createLayer(map, { | |
| user_name:'ernestomb', | |
| type:'cartodb', | |
| sublayers:[{ | |
| sql: clusterSQL, | |
| cartocss:cartoCSS, | |
| }] | |
| },{https:true}) | |
| .on('done', function(layer){ | |
| cdb.vis.Vis.addInfowindow(map,layer.getSubLayer(0), ['points_count', 'average'],{infowindowTemplate: $('#infowindow_template').html()}); | |
| }) | |
| .addTo(map); | |
| } | |
| window.onload = main; | |
| </script> | |
| </body> | |
| </html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment