Tag: CartoDB

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

Multiple Ring Buffer in CartoDB using PostGIS

I am fairly new to PostGIS and SQL queries. I had trouble finding examples demonstrating Multiple Ring Buffers in PostGIS–more specifically in a larger CartoDB SQL query. I needed to create a multi-ring buffer on the fly–which is why I couldn’t complete this in ArcMap or QGIS. Here is what I was attempting:

Multiple Ring Buffer Analysis Illustration

Multiple Ring Buffer Analysis Illustration

I contacted CartoDB support (very helpful) and they provided me with the following code which created the multi-ring buffers. However,  the rings overlapped.  If you are creating multi-ring buffers for aesthetic purposes, then there is no need to go any further than the code below. You can just rearrange them to get the correct look:  SELECT * FROM table_name ORDER BY position DESC

Overlapping Multiple Ring Buffers:

ALTER TABLE table_name ADD COLUMN position INTEGER;
INSERT INTO table_name (the_geom, position)
SELECT (ST_Buffer(the_geom::geography, 1000)::geometry), 1 FROM points_table;
INSERT INTO table_name (the_geom, position)
SELECT (ST_Buffer(the_geom::geography, 2000)::geometry), 2 FROM points_table;
INSERT INTO table_name (the_geom, position)
SELECT (ST_Buffer(the_geom::geography, 3000)::geometry), 3 FROM points_table;
INSERT INTO table_name (the_geom, position)
SELECT (ST_Buffer(the_geom::geography, 4000)::geometry), 4 FROM points_table;

The buffers were part of a larger query that later included an ST_Intersection function, so the buffers needed to be concentric rings that did not overlap. I came up with the following solution which basically buffers a buffer and then takes the difference to “cut out the overlapping portions”. This might not be the most elegant solution, but it worked perfectly for our application which generates these on the fly.

Multiple Ring Buffers:

ALTER TABLE table_name ADD COLUMN position INTEGER;
INSERT INTO table_name (the_geom, position)

SELECT

ST_Difference(ST_Transform(
(ST_Buffer(ST_SetSRID(ST_MakePoint(-77.4444325,37.5343969),4326)::geography,4000)::geometry),4326),ST_Transform(
(ST_Buffer(ST_SetSRID(ST_MakePoint(-77.4444325,37.5343969),4326)::geography,3000)::geometry),4326)) as the_geom_webmercator,
4 FROM points_table

Union all

SELECT

ST_Difference(ST_Transform(
(ST_Buffer(ST_SetSRID(ST_MakePoint(-77.4444325,37.5343969),4326)::geography,3000)::geometry),4326),ST_Transform(
(ST_Buffer(ST_SetSRID(ST_MakePoint(-77.4444325,37.5343969),4326)::geography,2000)::geometry),4326)) as the_geom_webmercator,
3 FROM points_table

Union all

SELECT

ST_Difference(ST_Transform(
(ST_Buffer(ST_SetSRID(ST_MakePoint(-77.4444325,37.5343969),4326)::geography,2000)::geometry),4326),ST_Transform(
(ST_Buffer(ST_SetSRID(ST_MakePoint(-77.4444325,37.5343969),4326)::geography,1000)::geometry),4326)) as the_geom_webmercator,
2 FROM points_table

Union all

SELECT
ST_Difference(ST_Transform(
(ST_Buffer(ST_SetSRID(ST_MakePoint(-77.4444325,37.5343969),4326)::geography,1000)::geometry),4326),ST_Transform(
(ST_Buffer(ST_SetSRID(ST_MakePoint(-77.4444325,37.5343969),4326)::geography,1)::geometry),4326)) as the_geom_webmercator,
1 FROM points_table

 

Hope this helps!

Multiple Ring Buffer

CartoDB and WordPress

I get really excited when you can incorporate maps into any part of your daily life. CartoDB just made it easier to upload web maps to WordPress! I love seeing ways that make web mapping less scary. Check out their blog on WordPress now brings you beautiful maps from CartoDB. Here is a map of the University of Richmond Running Trails, click on the trails to get the distance. Happy mapping everyone!

%d bloggers like this: