13

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

  1. 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:

enter image description here

2
  • ST_Dump could help, but not available in Spatialite. Try PostGIS. In PostGIS you can use ST_DumpPoints as there is no ST_DissolvePoints there.
    – Zoltan
    Dec 6 '20 at 7:59
  • @Zoltan: OK I created a table in pgAdmin 4 with create table vertex as select (ST_DumpPoints(geom)).geom from polygon and than in QGIS dragged this table as layer to the canvas. That works fine. However, it will not dynamically update as a virtual layer. Can't ST_DumpPoints() be used somehow in the virtual layer as well?
    – Babel
    Dec 6 '20 at 11:41
9

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.

input

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

result

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:

2
  • 1
    @Taras: Great, many thanks, works perfect! Indeed much more complicated than I initially thought.
    – Babel
    Dec 6 '20 at 21:35
  • 1
    you're amazing! Really great job, I just copy/paste and it works on my polygons! I only need to name the layer "polygons" and add a field named "id". No further adaption. I guess this is a fundemental query that could be very useful for many users - having direct access to the geometry of the vertices doesn't seem to be so exotic. Hope people vote up this answer. Great that it works so easy, even though the query itself takes some time to understand, but it works without understanding it in every last detail.
    – Babel
    Dec 8 '20 at 22:27
4

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
3
  • 1
    This works without changes even for complex multipolygons with several "nested" features (polygon with rings, inside of it another part of the mulit polygon which again contains rings that contain other polygon parts etc.). So I chaged the accepted answer to this one, even if the answer by @Taras works as well.
    – Babel
    Dec 19 '20 at 15:48
  • I am not sure if ST_SetSrid available in QGIS, I am getting an error: Query preparation error on PRAGMA table_info(_tview): no such function: ST_SetSrid. Even though it has to be in Spatialite functions.
    – Taras
    Jun 17 at 11:36
  • QGIS 3.18 : create a memory layer named test, create one or many features, go to virtual layers and query SELECT SetSRID(geometry, 4326) FROM test. For me, it works. Jun 17 at 12:36
1

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!=''))
2
  • A great solution, as well!
    – Babel
    Oct 31 at 16:42
  • 1
    Changed the regexp_replace pattern. Now it also works with multipolygons.
    – christoph
    Oct 31 at 17:39

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.