init
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<!--Edit the title of the page-->
<title>CartoDB Point Clustering</title>
<meta name="description" content="">
<meta name="author" content="">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<link rel="stylesheet" href="//libs.cartocdn.com/cartodb.js/v3/themes/css/cartodb.css" />
<!--[if lte IE 8]>
<link rel="stylesheet" href="//libs.cartocdn.com/cartodb.js/v3/themes/css/cartodb.ie.css" />
<![endif]-->
<!--Switch between the different themes changing the stylesheet below - light-theme.css |dark-theme.css -->
<link rel="stylesheet" href="main.css">
<style type="text/css">
.here ul {list-style: none;}
.here ul li {list-style: none; margin: 2px;}
</style>
</head>
<body>
<div class="map" id="map"></div>
<div class="sidepanel">
<div class="wrapper">
<div class="context subheader">
<p>Map created by <a href="//twitter.com/andrewxhill">@andrewxhill</a></p>
</div>
<h1>Point clustering</h1>
<p>This is a demonstration of point clustering using <a href="//cartodb.com">CartoDB</a>. The method uses an advanced mix of SQL, CartoCSS, and CartoDB.js, continue at your own risk...</p>
<p><label for="size">Cluster size</label> [<span class="size">48</span>]: <input type="range" name="size" id="cluster_size" value="48" min="20" max="60" step="2"></p>
<!--Copy and paste the div below for creating content blocks-->
<h3 class="here-title">What's here?</h3>
<div class="here">Click a point to find out!</div>
<div class="context footer">
<p>Create your maps with ease using <a href="//cartodb.com">CartoDB</a></p></p>
</div>
</div>
</div>
<script src="https://maps.googleapis.com/maps/api/js?sensor=false&v=3.8"></script>
<script src="//libs.cartocdn.com/cartodb.js/v3/cartodb.js"></script>
<script type="sql/html" id="sql_template">
WITH metatile_extent AS (
SELECT ST_SetSRID(!bbox!::box3d, 3857) as ext
),
filtered_table AS (
SELECT t.* FROM tornados_copy t, metatile_extent m WHERE t.the_geom_webmercator && m.ext
),
hgridA AS (SELECT CDB_HexagonGrid(ST_Expand(!bbox!, greatest(!pixel_width!,!pixel_height!) * {0}), greatest(!pixel_width!,!pixel_height!) * {0}) 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 FROM hgridA, filtered_table i where ST_Intersects(i.the_geom_webmercator, hgridA.cell) GROUP BY hgridA.cell) t WHERE points_count > 2*{0} ),
hgridB AS (SELECT CDB_HexagonGrid(ST_Expand(!bbox!, greatest(!pixel_width!,!pixel_height!) * 0.75*{0}), greatest(!pixel_width!,!pixel_height!) * 0.75*{0}) 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 FROM hgridB, filtered_table 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 > 0.5*{0} ),
hgridC AS (SELECT CDB_HexagonGrid(ST_Expand(!bbox!, greatest(!pixel_width!,!pixel_height!) * 24), greatest(!pixel_width!,!pixel_height!) * 0.5*{0}) 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 FROM hgridC, filtered_table 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 > GREATEST(0.1*{0}, 2) )
SELECT the_geom_webmercator, 1 points_count, cartodb_id, ARRAY[cartodb_id] as id_list, 'origin' as src, cartodb_id::text cdb_list FROM filtered_table 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, array_to_string(id_list, ',') FROM bigs
UNION ALL
SELECT *, 'mids' as src, array_to_string(id_list, ',') FROM mids
UNION ALL
SELECT *, 'smalls' as src, array_to_string(id_list, ',') FROM smalls
</script>
<script type="sql/html" id="cartocss_template">
#layer {
marker-width: {0} * 0.15;
marker-fill: #5CA2D1;
marker-opacity: 0.6;
marker-line-width: 0;
marker-allow-overlap: true;
marker-comp-op: dst-atop;
[src = 'smalls'] {marker-width: {0} * 0.35; }
[src = 'mids'] {marker-width: {0} * 0.7;}
[src = 'bigs'] { marker-width: {0} * 1.4; }
[zoom>11]{marker-width: {0};}
}
#layer::lables {
text-size: 0;
text-fill: black;
text-opacity: 0.8;
text-name: [points_count];
text-face-name: 'DejaVu Sans Book';
text-halo-fill: #fff;
text-halo-radius: 0;
// if points_count >= 100 we should also make text smaller //
[src = 'smalls'] {text-size: {0} * 0.2; text-halo-radius: 1; }
// if points_count >= 1000 we should also make text smaller //
[src = 'mids'] {text-size: {0} * 0.3; text-halo-radius: 1; }
// if points_count >= 10000 we should also make text smaller //
[src = 'bigs'] { text-size: {0} * 0.5; text-halo-radius: 1; }
text-allow-overlap: true;
[zoom>11]{text-size: {0} * 0.66;}
}
</script>
<script type="text/javascript">
var map;
function addCursorInteraction(layer) {
var hovers = [];
layer.bind('featureOver', function(e, latlon, pxPos, data, layer) {
hovers[layer] = 1;
if(_.any(hovers)) {
$('#map').css('cursor', 'pointer');
}
});
layer.bind('featureOut', function(m, layer) {
hovers[layer] = 0;
if(!_.any(hovers)) {
$('#map').css('cursor', 'auto');
}
});
layer.bind('featureClick', function(e, latlon, pxPos, data, layer) {
var list = data['cdb_list'].split(',');
if (list.length > 1){
$('.here-title').html(list.length+" features:");
$('.here').html("<ul></ul>");
for (i in list){
$(".here ul").append('<li><a href="#'+list[i]+'" class="cartodb_id" id="'+list[i]+'">'+list[i]+'</a></li></li>');
}
$('.cartodb_id').on('click', function(){
$.get("//andrew.cartodb.com/api/v1/sql?q=select cartodb_id, to_char(date, 'DD Mon YYYY') date, damage, ST_X(the_geom) lon, ST_Y(the_geom) lat from tornados_copy WHERE cartodb_id = " + $(this).attr('id'), function(ret) {
var lat = ret.rows[0].lat; delete ret.rows[0].lat;
var lon = ret.rows[0].lon; delete ret.rows[0].lon;
map.setView(new L.LatLng(lat, lon), 12);
$('.here-title').html("");
$('.here').html("");
for (i in ret.rows[0]){
$('.here').append("<h3>"+i+"</h3>");
$('.here').append(ret.rows[0][i]);
}
});
//zoom to 11
})
} else {
$('.here-title').html("");
$('.here').html("");
$.get("//andrew.cartodb.com/api/v1/sql?q=select cartodb_id, to_char(date, 'DD Mon YYYY') date, damage from tornados_copy WHERE cartodb_id = " + data['cartodb_id'], function(ret) {
for (i in ret.rows[0]){
$('.here').append("<h3>"+i+"</h3>");
$('.here').append(ret.rows[0][i]);
}
});
}
});
}
function main() {
// create leaflet map
map = L.map('map', {
zoomControl: true,
center: [35, -85],
zoom: 6
})
// add a base layer
L.tileLayer('//tile.stamen.com/toner/{z}/{x}/{y}.png', {
attribution: 'Stamen'
}).addTo(map);
var gridsize = 48;
var baseSql = $('#sql_template').html().format(gridsize);
var cartoCss = $('#cartocss_template').html().format(gridsize);
var sublayer;
$("#cluster_size").on('change',function(){
$('.size').html($("#cluster_size").val());
});
$("#cluster_size").on('mouseup',function(){
sublayer.setSQL($('#sql_template').html().format($("#cluster_size").val()));
sublayer.setCartoCSS($('#cartocss_template').html().format($("#cluster_size").val()))
});
// add cartodb layer with one sublayer
cartodb.createLayer(map, {
user_name: 'andrew',
type: 'cartodb',
sublayers: [{
sql: baseSql,
cartocss: cartoCss,
interactivity: 'cartodb_id, cdb_list'
}]
})
.addTo(map)
.done(function(layer) {
sublayer = layer.getSubLayer(0);
sublayer.setInteraction(true);
addCursorInteraction(sublayer);
});
}
String.prototype.format = (function (i, safe, arg) {
function format() {
var str = this,
len = arguments.length + 1;
for (i = 0; i < len; arg = arguments[i++]) {
safe = typeof arg === 'object' ? JSON.stringify(arg) : arg;
str = str.replace(RegExp('\\{' + (i - 1) + '\\}', 'g'), safe);
}
return str;
}
//format.native = String.prototype.format;
return format;
})();
// you could use $(window).load(main);
window.onload = main;
</script>
</body>
</html>
/* Change the styles below in order to customize your template */
body{font-family: Helvetica, Arial; font-weight: regular; font-size: 15px; color: #555; background-color: #FFF; margin: 0;}
h1{font-weight: bold; font-size: 31px; letter-spacing: -1px; color: #333; line-height: 33px;}
h3{font-weight: bold; font-size: 12px; color: #CCC; text-transform: uppercase; margin: 10px 0 0 0;}
p{margin: 8px 0 20px 0; line-height: 18px;}
a, a:visited{color: #397DB8; text-decoration: none;}
a:hover{text-decoration: underline;}
.wrapper{display: block; padding: 4px 30px 0 30px;}
.map{background-color:#eee; position: absolute; top: 0; left: 0; bottom: 0; width: 67%; *height:100%;}
.sidepanel{background-color:#FFF; position: absolute; top: 0; right: 0; bottom: 0; width: 33%; height: 100%; overflow: auto;}
.context{font-family: Helvetica, Arial; font-size: 13px; color: #999; padding: 10px 0 0 0;}
.subheader{border-bottom: 1px solid #ddd;}
.footer{border-top: 1px solid #ddd; margin-top: 30px;}
.titleBlock{text-align: right;}
/* Here are the styles that makes the template responsive */
@media only screen and (max-width: 768px) {
.map{position: inherit; height: 400px; width: 100%; display: block;}
.sidepanel{position: inherit; width: 100%;}
}
@media only screen and (max-width: 480px) {
.map {height: 300px;}
}