Habitat area within UTM grid in PostGIS

Question

I'm using PostgreSQL 14.1 with PostGIS 3.1 and I've got two tables malla, that contains a UTM grid of 10x10 km, and CLC18, that contains the habitat polygons of the Corine Land Cover project. Now I want to know the area of each habitat that is included in each cell of the grid, and I have built this code:

select 
    m.utmcode utm, 
    round(Sum(st_area(st_intersection(m.geom,c.geom)))/10000) area, 
    c.code_18 code
from public.malla m left join public.clc18 c
on st_intersects(m.geom,c.geom)
group by utm, code
;

but it returns several rows for each UTM cell, one for each habitat type. I'd rather have in columns all the Corine types/codes (there are 44 of them) and only one row for each UTM cell. What query should I use to get that?

Answer 1

I think what you are wanting can be accomplished by a pivot.

This SO post question covers it quite well.

https://stackoverflow.com/questions/69263964/how-to-pivot-in-postgresql

This can be accomplished through your select statement by specifying a filter for each Corine types you want to see. As the statement is executed each record is checked against the filter and aggregated. In code example below, (if I didn't make a mistake) it should return a row for each grid with, area, and then columns for each Corine type with a 1 or null; 1 for that type appearing in the grid.

select 
    m.utmcode utm, 
    round(Sum(st_area(st_intersection(m.geom,c.geom)))/10000) area, 
    MAX(CASE WHEN 'Code_18' = 211 THEN 1 END) AS Code_211,
    MAX(CASE WHEN 'Code_18' = 312 THEN 1 END) AS Code_312,
    -- Continue until all desired codes have a specifier.
from public.malla m left join public.clc18 c
on st_intersects(m.geom,c.geom)
group by utm
;

Answer 2

Following the clues given in @RomaH's answer, I've got what I wanted, using a subquery:

with cte as 
(select m.utmcode utm, 
c.code_18 code, 
round(Sum(st_area(st_intersection(m.geom,c.geom)))/10000) area 
from public.malla m left join public.clc18 c
on st_intersects(m.geom,c.geom)
group by utm, code)

select utm, 
MAX (CASE WHEN code = '111' THEN "area" END) AS cod_111,
MAX (CASE WHEN code = '112' THEN "area" END) AS cod_112,
-- [...]

from cte 
group by utm 
;