1

We are using Google MAP API V3 and CartoDB.

I am trying to achieve whole UK wide properties on the map with points and I have plotted all the points using cartodb.createLayer as per its Geo coordinate its working fine but I want to cluster all these points together on the map.

How can I cluster the data on map using cartodb.createLayer?

1 Answer 1

1

you can get an example here.

In the code I'm using two independent queries and using two different layers. You'd just need to reproduce it once.

Copying the code also here for the record: it includes the query that CartoDB uses to cluster but customized to my table "populated_places" and to my column "megacity". You'd need to change that for yours to work:

<!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>
1
  • Thanks, This work like genius. But as I am having huge amount of data on the data table around 1.4 million Geo coordinates and its going very slow while visualize the clustering on the map. Please suggest to increase the speed.
    – Nil
    Mar 23, 2015 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.