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_Transform — Returns a new geometry with its coordinates transformed to the SRID referenced by the integer parameter.
- ST_Centroid — Returns 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_Collect — Return a specified ST_Geometry value from a collection of other geometries.
- ST_Dump — Returns 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.