Merging and dissolving several polygon layers using PostGIS

I was asked this week to perform a fairly simple GIS task: buffer provincially significant wetlands by 120m, buffer other evaluated wetlands by 30m, and merge the results with an existing wetland layer. This would obviously involve a dissolve process since the buffered wetlands would likely create significant overlap between polygons. This task is a great opportunity to demonstrate a typical PostGIS workflow where a series of steps can be performed in a single query.

Although one could use shapefiles and clip, merge, and dissolve the contents, this would create several intermediate datasets. A good alternative is to use a spatial RDMS like PostGIS so that the whole process is reduced to a single query where the only output is a single table. Here’s how it works.

We are going to create a new table to store the results, so this will be a CREATE TABLE query. For the sake of this tutorial,  I assume you have a schema called “test”.

CREATE TABLE test.polygons_with_wetland_buffer AS (

WITH queries

Next you will need to create a few WITH queries. WITH queries are defined in the official PostgreSQL documentation as “auxiliary statements for use in a larger query”. Think of WITH queries as temporary tables that can be used later in the main query. You can have multiple WITH queries, but each must be separated with a closing bracket and comma. For the present query, we will create several WITH queries, namely:

• a boundary table,
• a table of buffered evaluated provincial wetlands that intersect the boundary,
• a table of buffered other evaluated wetlands that intersect the boundary,
• a table that unions the previous two wetland layers with another wetland layer,
• and a table that contains the dissolved geometry of the previous union table.

Let’s take a closer look at each.

The boundary WITH query simply extracts the boundary for the rest of the analysis. This is not necessary if you are merging the entirety of multiple datasets, but if you have a specific study area that needs to be extracted from a larger dataset (e.g. municipal boundaries), write something like the following.

WITH boundary AS (
SELECT *
FROM boundaries.municipalities
WHERE id=2), 
--or you could write WHERE somefield LIKE "something"),
--Note the comma after the closing bracket.

Using the Ontario wetlands layer, the next WITH query clips the evaluated provincial wetlands layer using ST_INTERSECTS with the boundary geometry. It then buffers the selected wetlands by 120m. I included a few extra fields in the SELECT line, but you really only need the geom field.

eval_provincial AS (
SELECT b.id, st_buffer(b.geom,120) as geom, signif
from boundary as a, hydrology.wetland_mnr as b
WHERE st_intersects(a.geom,b.geom) AND 
signif LIKE 'Evaluated-Provincial'),

Much like the previous WITH query, the next one selects and intersects other evaluated wetlands and buffers them by 30m.

eval_other AS (
SELECT b.id, st_buffer(b.geom,30) as geom, signif
from boundary as a, hydrology.wetland_mnr as b
WHERE st_intersects(a.geom,b.geom) 
AND signif LIKE 'Evaluated-Other'),

So, at this point in our query design we have created two new polygon tables using a common boundary, but we need to merge them into a single table. In the next WITH query, the two previous tables are combined with the wetland layer as a UNION.

union_dataset AS (
SELECT geom FROM eval_provincial
UNION
SELECT geom FROM eval_other
UNION
SELECT geom FROM hydrology.wetlands
),

If you stopped here (and wrote the last WITH query as the main SELECT query), you would see several overlapping polygons (i.e. a mess). The results now need to be dissolved into several polygons. To dissolve, one must first use ST_UNION on the geometry, then ST_DUMP to essentially redraw the polygons without overlap.

dissolved_dataset AS (
SELECT ST_Union(geom) as geom
 FROM union_dataset)

Dissolving in the main SELECT query

The main SELECT query will be the following. This uses ST_DUMP to change the geometry from multipolygon to polygon, and in so doing dissolve overlapping polygons into a single unified geometry.

 SELECT (ST_DUMP(geom)).geom::geometry(Polygon,26917) AS geom 
    FROM dissolved_dataset);

Lastly, the resulting table does not have a primary key (or any attribute data), so adding an ID field that is a PRIMARY KEY is always a good idea. This field should have the SERIAL data type.

ALTER TABLE test.polygons_with_wetland_buffer
ADD COLUMN id SERIAL PRIMARY KEY;

And that’s it! With this query you perform buffer, union, and dissolve functions in a single query that will take a few seconds to run. It’s a little verbose as a few of the WITH queries could be combined, but it works . The result is a single table that contains the original wetland data along with the buffered wetland layers.

Happy PostGISing!