Aggregating points in CartoDB–Using PostGIS


I recently starting working with a dataset that contained multiple points in the same city–In our case, Civil War prisons.  For instance, there where 11 different prisons in Richmond, Va (Image right). The problem is visualizing these at the national scale. When viewed at this scale, you get a mess of overlapping points, making it hard to understand and compare locations across the U.S.

Below are the PostGIS functions used in this query:

  • ST_TransformReturns a new geometry with its coordinates transformed to the SRID referenced by the integer parameter.
  • ST_CentroidReturns the geometric center of a geometry.
  • ST_Multi — Returns the geometry as a MULTI* geometry. If the geometry is already a MULTI*, it is returned unchanged.
  • ST_CollectReturn a specified ST_Geometry value from a collection of other geometries.
  • ST_DumpReturns a set of geometry_dump (geom,path) rows, that make up a geometry g1


The final query, which summed the columns and aggregated the geometry into a single point:






Copy & Past Version:

SELECT column, sum(field1) as field1, sum(field2) as field2, st_transform(ST_Centroid(ST_Multi(ST_Collect(f.the_geom))), 3857) as the_geom_webmercator 
FROM (SELECT column,field1, field2, cartodb_id (ST_Dump(the_geom)).geom As the_geom 
FROM tablename ) As f 
GROUP BY column

This query resulted in a nice cleanup of overlapping points. Second steps are going to include only utilizing this query at high zoom levels, as to provide the original detail when looking at particular cities.



Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: