qgis – How to Create Singlepart Polygons with Several Polygons in Virtual Layer QGIS

qgissqlvirtual-layer

I would like to automatize the creation of several singlepart polygons when polygons (from layer Polygons) are crossed by a the same polyline (from layer polylines), and fill the gap between like describe there.

enter image description here

I'm able to do it manually using this workflow :

  • Extract vertices Run Menu
  • Processing > Toolbox > Concave Hull (alpha shapes) ,value for Threshold = 0.14

But in this case there are several polylines, so it results the virtual layer should create several polygons (not only 1 as I do manually)

EDIT :

SELECT ST_UNION(pa.geometry) AS geometry
FROM Polygons AS pa, (
    SELECT ROW_NUMBER() OVER () AS id, geometry
    FROM Polylines
) AS pla
WHERE ST_INTERSECTS(pa.geometry, pla.geometry)
GROUP BY pla.id

For every single polyline (from layer Polylines), this code create a polygon : this is almost what I need.
But the problem is that it creates multipart polygons, and it doesn't fill the gap between them.
That's why I think Concave Hull (alpha shapes) ,value for Threshold = 0.14 would solve the issue, but I've no idea how to adapt the code with this.

Best Answer

If you are certain that your concavity factor works for you, it's a simple call to ST_ConcaveHull you're missing:

SELECT
  line.id,
  ST_ConcaveHull(
    ST_Collect(poly.geometry),
    0.14
  ) AS geometry
FROM
  Polygons AS poly
  JOIN
  (
    SELECT
      ROW_NUMBER() OVER() AS id,
      geometry
    FROM
      Polylines
  ) AS line ON
    ST_Intersects(poly.geometry, line.geometry)
GROUP BY
  line.id
;

or simpler - but not necessarily faster:

SELECT
  line.id,
  ST_ConcaveHull(
    ST_Collect(poly.geometry),
    0.14
  ) AS geometry
FROM
  Polygons AS poly
  JOIN
  Polylines AS line ON
    ST_Intersects(poly.geometry, line.geometry)
GROUP BY
  line.geometry
;

If those Polygons are strictly squares/rectangles like in your image there may be more performant, more precise workflows.

Related Question