Skip to content

Instantly share code, notes, and snippets.

@ernesmb

ernesmb/index.html

Last active March 22, 2016 10:14
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save ernesmb/348b9eed9ee4c7038fd7 to your computer and use it in GitHub Desktop.
Save ernesmb/348b9eed9ee4c7038fd7 to your computer and use it in GitHub Desktop.
Download ZIP
Aggregated content from clustered features II - Average Population
Raw
index.html
<!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
hgridA AS (SELECT CDB_HexagonGrid(ST_Expand(!bbox!, greatest(!pixel_width!,!pixel_height!) * 48), greatest(!pixel_width!,!pixel_height!) * 48) as cell),
bigs AS (SELECT * FROM (SELECT ST_Centroid(ST_Collect(i.the_geom_webmercator)) as the_geom_webmercator, count(i.cartodb_id) as points_count, 1 as cartodb_id, array_agg(cartodb_id) AS id_list, avg(pop_max) AS average FROM hgridA, (select * from ne_10m_populated_places_simple_7_copy) i where ST_Intersects(i.the_geom_webmercator, hgridA.cell) GROUP BY hgridA.cell) t WHERE points_count > 100 ),
hgridB AS (SELECT CDB_HexagonGrid(ST_Expand(!bbox!, greatest(!pixel_width!,!pixel_height!) * 36), greatest(!pixel_width!,!pixel_height!) * 36) as cell),
mids AS (SELECT * FROM (SELECT ST_Centroid(ST_Collect(i.the_geom_webmercator)) as the_geom_webmercator, count(i.cartodb_id) as points_count, 1 as cartodb_id, array_agg(cartodb_id) AS id_list, avg(pop_max) AS average FROM hgridB, (select * from ne_10m_populated_places_simple_7_copy) i where ST_Intersects(i.the_geom_webmercator, hgridB.cell) AND cartodb_id NOT IN (SELECT unnest(id_list) FROM bigs) GROUP BY hgridB.cell) t WHERE points_count > 25 ),
hgridC AS (SELECT CDB_HexagonGrid(ST_Expand(!bbox!, greatest(!pixel_width!,!pixel_height!) * 12), greatest(!pixel_width!,!pixel_height!) * 12) as cell),
smalls AS (SELECT * FROM (SELECT ST_Centroid(ST_Collect(i.the_geom_webmercator)) as the_geom_webmercator, count(i.cartodb_id) as points_count, 1 as cartodb_id, array_agg(cartodb_id) AS id_list, avg(pop_max) AS average FROM hgridC, (select * from ne_10m_populated_places_simple_7_copy) i where ST_Intersects(i.the_geom_webmercator, hgridC.cell) AND cartodb_id NOT IN (SELECT unnest(id_list) FROM bigs) AND cartodb_id NOT IN (SELECT unnest(id_list) FROM mids) GROUP BY hgridC.cell) t WHERE points_count > 1 )
SELECT the_geom_webmercator, 1 points_count, cartodb_id, ARRAY[cartodb_id], pop_max AS average, 'origin' as src FROM ne_10m_populated_places_simple_7_copy WHERE cartodb_id NOT IN (select unnest(id_list) FROM bigs) AND cartodb_id NOT IN (select unnest(id_list) FROM mids) AND cartodb_id NOT IN (select unnest(id_list) FROM smalls)
UNION ALL
SELECT *, 'bigs' as src FROM bigs
UNION ALL
SELECT *, 'mids' as src FROM mids
UNION ALL
SELECT *, 'smalls' as src FROM smalls
</script>
<style type="text/cartocss" id="cartoCSS">
#layer{
marker-width: 12;
marker-fill: #5CA2D1;
marker-line-width: 1.5;
marker-fill-opacity: 0.8;
marker-line-opacity: 0;
marker-line-color: #fff;
marker-allow-overlap: true;
[src = 'smalls'] {marker-line-width: 0; marker-width: 12; }
[src = 'mids'] {marker-line-width: 0; marker-width:36; }
[src = 'bigs'] {marker-line-width: 0; marker-width: 48; }
[ average <= 35676000] { marker-fill: #BD0026;}
[ average <= 300424] { marker-fill: #F03B20;}
[ average <= 111461] { marker-fill: #FD8D3C;}
[ average <= 47653] { marker-fill: #FECC5C;}
[ average <= 15601] { marker-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