Calculating area of intersecting polygons within severals overlapping buffers

Question

I am working with..

• an initial point layer (in blue) called : centroid_layer.shp
• an initial polygon layer (in green) of woods called : wood_zone.shp

..and I need to get the area of intersected wood_zone within each buffer (in red, created from centroid_layer), knowing that almost all my buffers are overlapping each others. Thus I need to include the wood_zone polygones as many times as the number of intersecting buffers.

I believed it is not possible to do only with QGIS processing and/or modeler (???) so I would like to solve it through PostGIS/PostgreSQL (9.6 version).

I have seen in a similar posted question from Anna and I was trying to proceed with SS_Rebelious solution using his SQL script not in Spatialite but in PostGIS/PostgreSQL (with the Query tool of pqAdmin 4) as following :

CREATE TABLE buff AS
SELECT gid, ST_Buffer(geom, 1000,'quad_segs=100') FROM centroid_layer ;

and then :

UPDATE centroid_layer
SET areacolumn = (SELECT ST_Area( 
                          (SELECT ST_Intersection(
                            (SELECT ST_Union(geom) FROM wood_zone), 
                            (SELECT geom FROM buff WHERE centroid_layer.gid = buff.gid)
                                                 )

                           )
                                )
                  );

but I have a problem after the UPDATE...SET... step. Indeed my resulting areacolumn is completly filled with 0.0 values (see below).

So I was wondering if I am doing something wrong or if there would be a different SQL script to use with Postgres-PgAdmin ?

---

EDIT : I have tried the same SQL script with a sample group of three buffers (associated with 3 related points) and I was able to collect the area values within my column named "areacolumn". At this point the only difference I see is that I used a different primary key, I mean I did not used the "gid" (automatically created by postgres/postGIS) primary key.

But I still do not understand... is the use of the "gid" as primary key a problem with such a SQL script ?

Answer 1

I believe that I have found the answer to my problem : The geometry column of my buffer table created from ST_Buffer is called st_buffer and not geom (as I thought it was the case for all geometry colums). The correct updating step is then :

UPDATE centroid_layer
SET areacolumn = (SELECT ST_Area( 
                          (SELECT ST_Intersection(
                            (SELECT ST_Union(geom) FROM wood_zone), 
                            (SELECT st_buffer FROM buff WHERE centroid_layer.gid = buff.gid)
                                                 )

                           )
                                )
                  );