5

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.

enter image description here

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 ?

enter image description here


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 ?

3

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)
                                                 )

                           )
                                )
                  );
  • 1
    If you want it to be geom you can alter your first query: CREATE TABLE buff AS SELECT gid, ST_Buffer(geom, 1000,'quad_segs=100') as geom FROM centroid_layer ; – HeikkiVesanto Aug 18 '17 at 15:20

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.