Last active
February 6, 2016 12:24
-
-
Save ernesmb/2064e4c63763d77768cc to your computer and use it in GitHub Desktop.
Aggregated content from clustered features
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 | 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"> | |
<span> custom </span> | |
<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>aggregated content</h4> | |
<p>{{content.data.contenido}}</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, array_agg(content1) AS content1 FROM hgridA, (select * from overlapping_points) 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, array_agg(content1) AS content1 FROM hgridB, (select * from overlapping_points) 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!) * 24), greatest(!pixel_width!,!pixel_height!) * 24) 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, array_agg(content1) AS content1 FROM hgridC, (select * from overlapping_points) 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], ARRAY[content1], array_to_string(ARRAY[content1], ',' ,'*') AS contenido, 'origin' as src FROM overlapping_points 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 *, array_to_string(content1, ',', '*') AS contenido, 'bigs' as src FROM bigs | |
UNION ALL | |
SELECT *, array_to_string(content1, ',', '*') AS contenido, 'mids' as src FROM mids | |
UNION ALL | |
SELECT *, array_to_string(content1, ',', '*') AS contenido, 'smalls' as src FROM smalls | |
</script> | |
<script> | |
var map=L.map('map', {center: [36,0],zoom:6}); | |
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(); | |
cartodb.createLayer(map, { | |
user_name:'ernestomb', | |
type:'cartodb', | |
sublayers:[{ | |
sql: clusterSQL, | |
cartocss:"#layer{ marker-width: 12; marker-fill: #FD8D3C; marker-line-width: 1.5; marker-fill-opacity: 1; marker-line-opacity: 1; marker-line-color: #fff; marker-allow-overlap: true;} #layer::labels { text-size: 6; text-fill: #fff; text-opacity: 0.8;text-name: [points_count]; text-face-name: 'DejaVu Sans Book'; text-halo-fill: #FFF; text-halo-radius: 0;text-allow-overlap: true;[zoom>11]{ text-size: 16; }[points_count = 1]{ text-size: 0; }}", | |
}] | |
},{https:true}) | |
.on('done', function(layer){ | |
cdb.vis.Vis.addInfowindow(map,layer.getSubLayer(0), ['points_count', 'contenido'],{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