QGIS - Intersection query in a Geopackage

Question

In QGIS 3.10.2, I want to calculate geometric intersections between two layers in a Geopackage like a view in PostgreSQL/PostGIS. The first layer is composed with features, a name attribute and a geometry.

LayerA

fid | name
----------
1   | A
2   | B
3   | C
...

The second layer is composed with 3 overlapping features, an distance attribute (originally it's a buffer) and a geometry.

LayerB

fid | distance
----------
1   | 5
2   | 20
3   | 50

For each features of LayerA I want to calculate intersections with LayerB by attributes (even if this intersection is null). For example:

name | distance5 | distance20 | distance50
------------------------------------------
A    | 0.005     | 0.01       | 0.05
B    | 0         | 0.015      | 0.02
C    | 0         | 0          | 0.3
...

I tried a query with a CASE but I have intersections results only for one distance (the largest, because of overlapping I think).

SELECT
a.name,
CASE WHEN b.distance = 50 THEN ST_Area(ST_Intersection(a.geom, b.geom)) END AS distance50,
CASE WHEN b.distance = 20 THEN ST_Area(ST_Intersection(a.geom, b.geom)) END AS distance20,
CASE WHEN b.distance = 5 THEN ST_Area(ST_Intersection(a.geom, b.geom)) END AS distance5
FROM LayerA a
INNER JOIN LayerB b ON ST_Intersects(a.geom, b.geom)
GROUP BY a.name
ORDER BY a.name

An other method return me results for each feature of LayerA by LayerB attributes, but I need to pivot results to display them as column and I don't know if it's possible with a Geopackage.

SELECT 
row_number() OVER () AS id,
a.name,
b.distance,
ROUND(ST_Area(ST_Intersection(a.geom, b.geom))/10000,4) as area_ha,
ST_AsText(ST_Multi(ST_Intersection(a.geom, b.geom))) as geom
FROM LayerA a, LayerB b
WHERE  a.name <> b.distance AND ST_Intersects(a.geom, b.geom)
ORDER BY a.name

What the best way to calculate area of geometric intersections between two layers, group by a layer's attribute and with overlapping features of a second layer like this ?

name | distance5 | distance20 | distance50
------------------------------------------
A    | 0.005     | 0.01       | 0.05
B    | 0         | 0.018      | 0.02
C    | 0         | 0          | 0.3
...

Answer 1

Solution

Add an aggregate function (sum for example) around your case/when expression

SELECT
a.name,
SUM(CASE WHEN b.distance = 50 THEN ST_Area(ST_Intersection(a.geometry, b.geometry)) END) AS distance50,
SUM(CASE WHEN b.distance = 20 THEN ST_Area(ST_Intersection(a.geometry, b.geometry)) END) AS distance20,
SUM(CASE WHEN b.distance = 5 THEN ST_Area(ST_Intersection(a.geometry, b.geometry)) END) AS distance5
FROM LayerA a
INNER JOIN LayerB b ON ST_Intersects(a.geometry, b.geometry)
GROUP BY a.name
ORDER BY a.name

NB : Usage of coalesce

You may want to add coalesce.

coalesce(sum(case ...end),0) to replace NULL value by 0

Explanation

This has nothing to do with the overlaping. If you don't give an aggregate function with a group by, it will return the first row it will find.

The query without the group by:

SELECT
a.name,
CASE WHEN b.distance = 50 THEN ST_Area(ST_Intersection(a.geom, b.geom)) END AS distance50,
CASE WHEN b.distance = 20 THEN ST_Area(ST_Intersection(a.geom, b.geom)) END AS distance20,
CASE WHEN b.distance = 5 THEN ST_Area(ST_Intersection(a.geom, b.geom)) END AS distance5
FROM LayerA a
INNER JOIN LayerB b ON ST_Intersects(a.geom, b.geom)

should return something like this :

name	distance50	distance20	distance5
A	0.05	NULL	NULL
A	NULL	0.01	NULL
A	NULL	NULL	0.005
B	NULL	NULL	NULL
B	NULL	0.018	NULL
B	0.02	NULL	NULL
...	...	...	...

So if you group by name it will keep one row for each unique name.