Last active
January 17, 2017 18:44
-
-
Save iriberri/1a710e4bb909482c7f57 to your computer and use it in GitHub Desktop.
Cluster two layers by category
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>Leaflet multilayer example | CartoDB.js</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="http://libs.cartocdn.com/cartodb.js/v3/themes/css/cartodb.css" /> | |
<!--[if lte IE 8]> | |
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v3/themes/css/cartodb.ie.css" /> | |
<![endif]--> | |
</head> | |
<body> | |
<div id="map"></div> | |
<!-- include cartodb.js library --> | |
<script src="http://libs.cartocdn.com/cartodb.js/v3/cartodb.js"></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 FROM hgridA, (select * from ne_10m_populated_places_simple_14 WHERE megacity = 0) 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 FROM hgridB, (select * from ne_10m_populated_places_simple_14 WHERE megacity = 0) 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 FROM hgridC, (select * from ne_10m_populated_places_simple_14 WHERE megacity = 0) 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 > 5 ) | |
SELECT the_geom_webmercator, 1 points_count, cartodb_id, ARRAY[cartodb_id], 'origin' as src FROM ne_10m_populated_places_simple_14 WHERE megacity = 0 AND 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> | |
<script type="sql/html" id="sql_template_b"> | |
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 FROM hgridA, (select * from ne_10m_populated_places_simple_14 WHERE megacity = 1) 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 FROM hgridB, (select * from ne_10m_populated_places_simple_14 WHERE megacity = 1) 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 FROM hgridC, (select * from ne_10m_populated_places_simple_14 WHERE megacity = 1) 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 > 5 ) | |
SELECT the_geom_webmercator, 1 points_count, cartodb_id, ARRAY[cartodb_id], 'origin' as src FROM ne_10m_populated_places_simple_14 where megacity = 1 and 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> | |
<script> | |
function main() { | |
// create leaflet map | |
var map = L.map('map', { | |
zoomControl: false, | |
center: [35, 0], | |
zoom: 2 | |
}) | |
// add a base layer | |
L.tileLayer('http://tile.stamen.com/toner/{z}/{x}/{y}.png', { | |
attribution: 'Stamen' | |
}).addTo(map); | |
var baseSqlA = $('#sql_template_a').html(); | |
var baseSqlB = $('#sql_template_b').html(); | |
// add cartodb layer with one sublayer | |
cartodb.createLayer(map, { | |
user_name: 'iriberri', | |
type: 'cartodb', | |
sublayers: [{ | |
sql: baseSqlA, | |
cartocss: "#layer { text-size: 12; text-fill: blue; text-name: [points_count]; text-face-name: 'DejaVu Sans Book'; text-halo-fill: #fff; text-halo-radius: 1; [src = 'mids'] {text-size: 36;} [src = 'smalls'] {text-size: 24; } [src = 'bigs'] { text-size: 48; } }" | |
}] | |
}) | |
.addTo(map) | |
.done(function(layer) { | |
}); | |
cartodb.createLayer(map, { | |
user_name: 'iriberri', | |
type: 'cartodb', | |
sublayers: [{ | |
sql: baseSqlB, | |
cartocss: "#layer { text-size: 12; text-fill: red; text-name: [points_count]; text-face-name: 'DejaVu Sans Book'; text-halo-fill: #fff; text-halo-radius: 1; [src = 'mids'] {text-size: 36;} [src = 'smalls'] {text-size: 24; } [src = 'bigs'] { text-size: 48; } }" | |
}] | |
}) | |
.addTo(map) | |
.done(function(layer) { | |
}); | |
} | |
// you could use $(window).load(main); | |
window.onload = main; | |
</script> | |
</body> | |
</html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment