Aggregating points in CartoDB–Using PostGIS

Prisons_National_edited

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:

aggregate_code

 

 

 

 

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.

 

prison_locations

Leave a Reply

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

%d bloggers like this: