CARTO: Aggregating data on SQL and infowindows

Example on how to aggregate data using spatial SQL and then show the also attributes on CARTO BUILDER infowindow.

Based on a sample dataset with images I will first reduce the geographical precission so they collide into a grid. After that using the json_agg function we join the urls into a single field along with the counts.

WITH data as (
  SELECT cartodb_id,
         the_geom,
         ST_SnapToGrid(the_geom_webmercator,200000) as the_geom_webmercator,
         url_t
    FROM jsanz.flickr_interesting
   WHERE the_geom is not null
)
  SELECT min(cartodb_id) as cartodb_id,
         CDB_TransformToWebmercator(the_geom_webmercator) as the_geom,
         the_geom_webmercator,
         json_agg(url_t) as urls,
         count(*) as counts
    FROM data
GROUP BY the_geom_webmercator

With a SQL like this we can use an infowindow in BUILDER where we iterate over the urls field to render the different images in a single infowindow.

<div class="CDB-infowindow CDB-infowindow--light js-infowindow">
  <div class="CDB-infowindow-close js-close"></div>
  <div class="CDB-infowindow-container">
    <div class="CDB-infowindow-bg">
      <div class="CDB-infowindow-inner">
        <ul class="CDB-infowindow-list js-content">
          <li class="CDB-infowindow-listItem">
              <h5 class="CDB-infowindow-subtitle">Pictures</h5>


{{#urls}}
<img width="50px" src="{{.}}"/>
{{/urls}}
{{^urls}}
loading...
{{/urls}}


          </li>
        </ul>
      </div>
    </div>
    <div class="CDB-hook">
      <div class="CDB-hook-inner"></div>
    </div>
  </div>
</div>

Mind the {{#urls}}...{{/urls}}{{^urls}}loading...{{/urls}} structure to iterate over the inner rows of the aggregation. Of course you can use the item ({{.}}) not just on images but on any other HTML entity and combine it with lists, etc.

A working example of this technique is available here

jsanz/README.md

CARTO: Aggregating data on SQL and infowindows