Convert vertices of polygon features to points using Virtual Layer in QGIS

Question

I have a polygon layer and want to use a Virtual Layer that creates points from the vertices of the polygon layer. The question is how to write a query that does this?

I am aware of the tool "Extract vertices", but that is not what I am looking for.

What I tried:

1.  select fid, nodes_to_points(p.geometry) as vertices
    from polygon as p
   

nodes_to_points() is a QGIS expression, but the documentation says:

Functions of QGIS expressions can also be used in a virtual layer query.

However, it does not say anything how to include it in the (SQL) query syntax. Update: it seems that not all QGIS expressions can be used in virtual layer, see here for a list of functions that can be used: Listing all functions available in QGIS's Virtual Layer

2. I also looked in the SpatiaLite 4.2.0 SQL functions reference list, but could not find any function to create individual points for each vertex. The closest I came was using ST_DissolvePoints():

    select fid, ST_DissolvePoints (p.geometry) as vertices
    from polygon as p
   

This creates the visual effect of vertices, but not as individual points, but a single multi-point feature per polygon:

Answer 1

Unfortunately the ST_Dump() and ST_DumpPoints() functions are not yet implemented in Virtual Layer. Therefore, I am offering a workaround on how to extract vertices of polygon features.

This solution incorporates several functions, namely NumInteriorRings(), ST_InteriorRingN(), ST_ExteriorRing(), ST_PointN(), and ST_NPoints().

Someone can find this solution not perfect in terms of performance and complexity, because of the vast output of the CROSS JOIN clause that produces redundant indexes (for interior rings as well as for vertices) that are actually required. If somebody knows how to unravel it, please feel free to improve the query or give a hint.

---

Let's assume there is a polygon layer called 'polygons', see image below.

With the following query

-- generating the second series required to extract all vertices from polygons' contours
WITH RECURSIVE generate_series2(vert) AS (
    SELECT 1
    UNION ALL
    SELECT vert + 1
    FROM generate_series2, max_num_points_per_feature
    WHERE vert + 1 <= stop2
),

-- finding max number of vertices in all features
max_num_points_per_feature AS (
    SELECT MAX(st_npoints(geom1)) AS stop2
    FROM inter_outer_contours
),

-- union of geometries of both outer and inner contours, represented as polylines
inter_outer_contours AS (
    -- interior rings from all polygons
    WITH interior AS (
        -- generating the first series required to extract all interior rings
        WITH RECURSIVE generate_series1(ring) AS (
            SELECT 1
            UNION ALL
            SELECT ring + 1
            FROM generate_series1, max_num_rings_per_polys
            WHERE ring + 1 <= stop1
        ),

        -- finding max number of interior rings within all polygons
        max_num_rings_per_polys AS (
            SELECT MAX(numrings) AS stop1
            FROM num_rings_per_poly
        ),

        -- finding how many interior rings each polygon has
        num_rings_per_poly AS (
            SELECT id, NumInteriorRings(geometry) AS numrings
            FROM "polygons"
            GROUP BY id
        )
        
        -- query to extract all interior rings from all polygons
        SELECT p.id AS origin, -- a field represents the original polygon id
               'interior' AS info, -- a text field represents the interior attribute
               interior_ring_n(geometry, ring) AS geom1 -- setting geometry for a polyline
        FROM "polygons" AS p
        CROSS JOIN generate_series1
        WHERE geom1 IS NOT NULL -- no null geometries accepted, needed because of the cross join
    ),

    -- exterior rings from all polygons
    exterior AS (
        SELECT p.id AS origin, -- a field represents the original polygon id
               'exterior' AS info, -- a text field represents the exterior attribute 
               st_exteriorring(geometry) AS geom1 -- setting geometry for a polyline
        FROM "polygons" AS p
    )

    -- a union between interior and exterior rings from all polygons
    SELECT *
    FROM interior
    UNION
    SELECT *
    FROM exterior
)

-- query to achieve all vertices from all polygons
SELECT ioc.origin,
       ioc.info,
       gs2.vert, -- a field represents the vertice index
       st_pointn(ioc.geom1, gs2.vert) AS geom2 -- setting geometry for a point
FROM inter_outer_contours AS ioc
JOIN generate_series2 AS gs2
WHERE geom2 IS NOT NULL -- no null geometries accepted, needed because of the cross join

it is possible to get all the vertices

The result matches the output of the "Extract vertices" geoalgorithm.

---

Explanations:

• In the query above do not forget to change four times "polygons" string into your actual polygon layer name

---

TODO:

• extend the explanation section
• try with multipolygons
• add a new field "id"

---

References:

• Using RECURSIVE in Virtual Layer

Answer 2

Here my solution :

---

Requirements

• QGIS 3.x
• A (multi)-polygon layer with an id field with distinct values

---

Features

• One query
• Works with multipolygons
• Configure layer name at only one place
• Output the layer id, part number, ring number and vertex number
• Drop the last duplicate vertex (in a polygon, vertex start = vertex end)

---

1. Open QGIS, go to Database > Database > DB Manager... > Virtual Layers
2. Open a new SQL Window
3. Copy the SQL script below and replace, in the lyr part, polygons with your real layer name (or rename under QGIS your layer polygons) :

-- list parts
WITH RECURSIVE gs_part(id, part) AS (
    SELECT conf_p.id, conf_p.start
    FROM conf_p
    UNION ALL
    SELECT conf_p.id, part + 1
    FROM gs_part gs, conf_p
    WHERE
        gs.id = conf_p.id
        AND gs.part + 1 <= conf_p.stop
),
-- list interior rings
gs_ring(id, part, ring) AS (
    SELECT conf_i.id, conf_i.part, conf_i.start
    FROM conf_i
    UNION ALL
    SELECT conf_i.id, conf_i.part, ring + 1
    FROM gs_ring gs, conf_i
    WHERE
        gs.id = conf_i.id
        AND gs.part = conf_i.part
        AND gs.ring + 1 <= conf_i.stop
),
-- list vertices
gs_vert(id, part, ring, vert) AS (
    SELECT conf_v.id, conf_v.part, conf_v.ring, conf_v.start
    FROM conf_v
    UNION ALL
    SELECT conf_v.id, conf_v.part, conf_v.ring, vert + 1
    FROM gs_vert gs, conf_v
    WHERE
        gs.id = conf_v.id
        AND gs.part = conf_v.part
        AND gs.ring = conf_v.ring
        AND gs.vert + 1 < conf_v.stop
),
--
parts AS (
    SELECT lyr.id, gs_part.part, st_geometryn(lyr.geometry, gs_part.part) AS geometry
    FROM gs_part, lyr
    WHERE lyr.id = gs_part.id
),
--
rings AS (
    SELECT
        'interior' AS info,
        parts.id,
        parts.part,
        gs_ring.ring,
        interior_ring_n(parts.geometry, ring) AS geometry
    FROM gs_ring, parts
    WHERE
        gs_ring.ring > 0
        AND gs_ring.id = parts.id
        AND gs_ring.part = parts.part
    UNION ALL
    SELECT
        'exterior',
        parts.id,
        parts.part,
        0,
        exterior_ring(parts.geometry)
    FROM parts
),
-- configuration
-- for parts
conf_p AS (
    SELECT id, 1 AS start, st_numgeometries(geometry) AS stop
    FROM lyr
),
-- for interior rings
conf_i AS (
    SELECT id, part, 0 AS start, num_interior_rings(geometry) AS stop
    FROM parts
),
-- for vertices
conf_v AS (
    SELECT id, part, ring, 1 AS start, st_npoints(geometry) AS stop
    FROM rings
),
-- for layer
lyr AS (
    SELECT
        'polygons' AS lyr_name, -- ## replace here with the 'layer name' ##
        p.*
    FROM polygons p --  ## replace here with the layer name ##
),
-- get layer crs
crs AS (
    SELECT CAST(
        SUBSTR(layer_property(lyr_name, 'crs'), 6, 15)
        AS integer
    ) AS id
    FROM lyr
    LIMIT 1
)

SELECT
    rings.info,
    rings.id,
    rings.part,
    rings.ring,
    gs_vert.vert AS vertex,
    SetSRID(st_pointn(rings.geometry, gs_vert.vert), crs.id) AS geometry
FROM gs_vert, rings, crs
WHERE
    gs_vert.id = rings.id
    AND gs_vert.part = rings.part
    AND gs_vert.ring = rings.ring

Answer 3

Since I was not able to suxessfully run the given SQL scripts in QGIS 3.22.0, I came up with another solution using regexp_replace and a common table expression to split the WKT polygon representation into single vertices. (https://stackoverflow.com/questions/24258878/how-to-split-comma-separated-value-in-sqlite)

The solution will work with (multi)polygons including holes. It uses a polygon layer polygons with a field name for grouping.

SELECT row_number() over(partition by name) as id,st_x(geom) as x,st_y(geom) as y,* 
FROM 
(SELECT 
    ST_GeomFromText('POINT('|| word ||')',CAST(SUBSTR(layer_property('polygons','crs'), 6, 15) AS integer)) as geom,
    name 
FROM 
(WITH split(word, name, str) AS (
    SELECT 
        '',
        name, 
        regexp_replace(AsText(geometry),'[^\d\.\s,]+|(?<=,)\s+','')||',' 
    FROM polygons
    UNION ALL
    SELECT
        substr(str, 0, instr(str, ',')), 
        name,
        substr(str, instr(str, ',')+1)
    FROM split
    WHERE str!='') SELECT distinct name,rtrim(word,',') as word FROM split WHERE word!=''))