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

Leave a Reply

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

%d bloggers like this: