QGIS Virtual Layer – Creating Difference of Two Polygon Layers Using Virtual Layer in QGIS

Question

I am trying to learn the usage of Virtual Layers in QGIS but I can't find a solution for a problem that seems very simple.

I have two polygon layers and I want to have the difference.

Polygon layer 1:

A "cut" layer:

And this is what I want to get:

How should my query look like to achieve the desired output?

Answer 1

Another solution is to use the ST_Union() inside of the query (it was also mentioned in
@geozelot's comment), because features from a "cut" layer need to become one geometry (of type MULTIPOLYGON). This idea was taken from this thread : More on cutting polygons with polygons, in PostGIS.

Let's assume there are two polygon layers called 'grid_test' (green) with nine features and 'grid_test2' (orange) with five features in it, see image below.

So, your query may look like this:

WITH union_gd2 AS (
    SELECT
        ST_Union(gt2.geometry) as geometry
    FROM
        "grid_test2" AS gt2
    )

SELECT
    gd1.id,
    st_difference(gd1.geometry, ugd2.geometry) AS geometry
FROM
    "grid_test" AS gd1,
    "union_gd2" AS ugd2
WHERE
    st_isvalid(st_difference(gd1.geometry, ugd2.geometry))

or like this:

SELECT
    gd1.id,
    st_difference(gd1.geometry, union_gd2.geometry) AS geometry
FROM
    "grid_test" AS gd1,
    (SELECT
        ST_Union(gt2.geometry) as geometry
    FROM
        "grid_test2" AS gt2
    ) AS union_gd2
WHERE
    st_isvalid(st_difference(gd1.geometry, union_gd2.geometry))

and then get the final output

---

References:

• Select empty Postgis (Postgresql) geometry column