Category: Open Source

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

A great open source tool for any GIS user

Before my time at the University of Richmond, the only mention of open source software was from computer science majors and programmers. Open source naturally seemed intimidating, since it was something new and mysterious, and in my mind sub-par to it’s proprietary counterpart. During my education, both universities I attended had a ESRI site license and rarely touched on open source GIS tools or software. The DSL has always leaned towards using open source software and its philosophy, which has been a great learning experience for me. I have begun to see the benefit in using both open source and proprietary software depending on the task.

While attending the VAMLIS conference last week, I attended a workshop presented by Jonah Adkins, a Sr. GIS Analyst for GISi. It was a great overview of open street map with some very useful open source tools on the web. Working with historical data, we tend to digitize a lot of polygons at the DSL. When serving these polygons up on the web it’s nice to generalize these for enhanced viewing optimization.  I have always had trouble simplifying polygons in Arc effectively and have dreaded this process every time I need to generalize a new data-set. Below is a great time saving tool for any GIS user struggling with simplifying polygons and wondering what tolerance levels to use.

When Jonah showed Mapshaper, “A tool for topologically aware shape simplification. Reads and writes Shapefile, GeoJSON and TopoJSON formats” tool at the conference, I was ecstatic! Not only can you upload a Shapefile, you can see in real time the simplification and the percent change from the original polygons.  It is one of the most simple, yet effective tools I have ever used. It is as simple as this.

1. Click on the link above. 

2. Configure your setting and upload file.

3. Slide simplify bar to simplify.

4. Export to Shapefile, GeoJSON and TopoJSON

5. Receive zipfile and enjoy simplify polygons.

6. Repeat!

 

 

Untitled-1

 

So don’t be hesitant of open source options like I was. They can make your life a lot easier and broaden your knowledge while simplifying your workflows. Using a mix of proprietary and open source software/tools can be a powerful combination!

 

 

 

%d bloggers like this: