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 ?