# QGIS - Intersection query in a Geopackage

In `QGIS 3.10.2`, I wan't to calculate geometric intersections beetween 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 wan't to calculate intersections with LayerB by attributes (even if this instersection 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 tryed 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
...
``````

# Solution

``````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.

• That's it, I need an aggregate function like `SUM`. Feb 12, 2021 at 11:39