I once had a long data processing job that created multiple database snapshots on a PostgreSQL instance. However, this process was crashing sometimes leaving the databases behind. I had to automate the process of dropping the database where databases with names matching a certain pattern would be dropped. Bash script came in handy!
In the script, I write to a text file a series of SQL
drop database statements and then execute each of the line using
psql. It tool me a while to get it right as I wasn’t able to find a ready-to-use Bash script online, so this may be helpful to others.
If you are a user of GDAL installation (which includes OGR), then you could take advantage of running OGR command line tools such as
ogr2ogr which are very handy and often more efficient in data processing comparing to Python scripts written using
fiona. As a rule of thumb, if your Bash script is within 20-30 lines of code then you are doing okay. If it gets longer, it is worth switching to Python for readability and maintainability of the code.
Make sure to review the Python GDAL/OGR Cookbook!, it has a ton of useful examples. Below are some snippets you can use; they will also help you learn Bash if you are not familiar with it yet.
If you will be writing a lot of Bash, I suggest using an IDE that supports it. I have been using PyCharm with an amazing plugin BashSupport. It takes the experience of writing Bash scripts to a new level. It provides syntax highlight, auto-completion, and hover hints.
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.