Recently I needed to generate multiple ring buffers around some point features. This can be done using a dozen of tools – Multiple Ring Buffer geoprocessing tool in ArcGIS, using
arcpy to generate multiple buffer polygons and merging them into a single feature class using the
buffer() method of
arcpy.Geometry() object, or by using open source GIS tools such as QGIS. This is also possible to achieve using relational database that has support for the spatial functions. In this post, I would like to show you how this can be done using the
ST_Buffer spatial function in PostGIS and SQL Server.
In order to generate multiple buffer distance values (for instance, from 100 to 500 with the step of 100) in SQL Server, I would probably need use
CTE or just create a plain in-memory table using
declare; in other words, this is what it takes to run
range(100, 501, 100) in Python.
In the gist below, there are two ways to generate multiple buffers – using the plain table and the
Generating a sequence of distances in Postgres is a lot easier thanks to the presence of the
generate_series function which provides the same syntax as
range in Python.