4

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.

2 Answers 2

2

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.

4
  • hello, 1) it doesn't create gemeotry : geometry is a row in attribute table, but entities doesn't have geometries. It results it doesn't display vectors. 2) about Row_Number : depending on the format of the vectors, some of them doesn't have ID auto. That's why I prefer always use Row_Number, to number bymyself the lines. 3) I'm very interested into more precise workflows, because my polygons are squares, so it could work perfectly ! Thanks for your help
    – zlikotp
    Jul 15, 2023 at 8:15
  • I've edited my initial post, in order to show you the result on my screen. as you can see, the icon of the layer "virtual layer" created by your code, doesn't display a polycon icon; also, the right windows is the attribute table which show an empty row geometry. It results it doesn't display any vector as it should. any idea?
    – zlikotp
    Jul 15, 2023 at 10:57
  • I wonder if we need to extract polygon geometry using : ST_CollectionExtract before the ST_ConcaveHull
    – zlikotp
    Jul 16, 2023 at 14:58
  • it looks like ST_ConcavHull (alpha) need to work with points vectors. I guess we first have to extract vertices points from polygons, in order to use ST_ConcavHull , or we should reproduce the function "Concave hull (nearest neighboor) in order to directly work from polygon. Don't you think?
    – zlikotp
    Jul 16, 2023 at 15:38
1
+500

My test-setup:

  1. Line-Memory-Layer with test-lines, Layername: Polylines
  2. Polygon-Layer with id-column and Test-Polygons. Layername: Polygons.

I created a virtual layer in the QGIS DB-Manager with this query:

See attached screenshots.


with

line_layer_with_id as
(
     select  ROW_NUMBER() OVER() as line_id, geometry
     FROM Polylines l
),

intersect_subquery as 
    (
     select l.line_id, ST_UNION(p.geometry) as geometry FROM
          Polygons p, line_layer_with_id l
      where
        ST_Intersects(p.geometry, l.geometry)
        GROUP BY l.line_id
    ),

extract_vertices as
    (
    SELECT
         line_id, CastToMultiPoint(ST_Union(ST_DissolvePoints( sub.geometry))) as geometry
        FROM
        
          intersect_subquery sub
          GROUP BY line_id)
    select line_id, st_concavehull(ev.geometry, 5) as geometry
    from extract_vertices ev
    GROUP BY line_id;

You probably have to test some settings for the concavehull parameter and the optional boolean parameter if you want to allow holes or not. ( st_concavehull(ev.geometry, 2.5,False) for example ) enter image description here

6
  • hi, and thanks for answer. In order to work without the need of ID colomn, I use : ROW_NUMBER() OVER() AS id But in your code, I do not know what can I change to make it working?
    – zlikotp
    Jul 18, 2023 at 9:02
  • @zlikotp: therefore just replace the 3rd line with select ROW_NUMBER() OVER() AS id , p.geometry FROM instead of select p.id, p.geometry FROM
    – Thomas B
    Jul 18, 2023 at 12:18
  • 1
    @Tomas B: the code works but it creates only 1 multipart polygon instead of several singlepart polygon (1 polygon for every polyline). I've added my initial post with screenshoots of the result I get with your code.
    – zlikotp
    Jul 18, 2023 at 12:40
  • @zlikotp: I changed the answer to allow generating one convexhull for every intersecting line. just try to adjust the convexhull parameter so it fit's the best for your datasets.
    – Thomas B
    Jul 18, 2023 at 13:46
  • I tried with replacing select l.id , ST_UNION(p.geometry) as geometry FROM by select ROW_NUMBER() OVER() AS id , ST_UNION(p.geometry) as geometry FROM , but I get the same issue : "Query preparation error on PRAGMA table_info(_tview): no such column: l.id"
    – zlikotp
    Jul 18, 2023 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.