5

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

intersection

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

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.