Convierta vértices de entidades poligonales en puntos usando Virtual Layer en QGIS

Solución:

Desafortunadamente, el ST_Dump() y ST_DumpPoints() Las funciones aún no están implementadas en Virtual Layer. Por lo tanto, ofrezco una solución alternativa sobre cómo extraer vértices de entidades poligonales.

Esta solución incorpora varias funciones, a saber NumInteriorRings(), ST_InteriorRingN(), ST_ExteriorRing(), ST_PointN(), y ST_NPoints().

Alguien puede encontrar que esta solución no es perfecta en términos de rendimiento y complejidad, debido a la gran cantidad de CROSS JOIN cláusula que produce índices redundantes (tanto para anillos interiores como para vértices) que son realmente necesarios. Si alguien sabe cómo desentrañarlo, no dude en mejorar la consulta o dar una pista.

---

Supongamos que hay una capa de polígono llamada 'polygons', vea la imagen a continuación.

Con la siguiente consulta

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

es posible obtener todos los vértices

El resultado coincide con la salida del geoalgoritmo "Extraer vértices".

---

Explicaciones:

• En la consulta anterior no lo olvide cambiar cuatro veces "polygons" string en el nombre real de la capa de polígono

---

HACER:

• ampliar la sección de explicación
• prueba con multipolígonos
• agregar un nuevo campo "id"

---

Referencias:

• Usando RECURSIVE en la capa virtual

Aquí mi solución:

---

Requisitos

• QGIS 3.X
• Una capa (multi) poligonal con una id campo con distinto valores

---

Características

• Una consulta
• Funciona con multipolígonos
• Configure el nombre de la capa en un solo lugar
• Salida de la capa id, número de pieza, número de anillo y número de vértice
• Suelta el último vértice duplicado (en un polígono, inicio del vértice = final del vértice)

---

1. Abra QGIS, vaya a Base de datos>Base de datos>Administrador de bases de datos ...>Capas virtuales
2. Abra una nueva Ventana SQL
3. Copie el script SQL a continuación y reemplácelo, en el lyr parte, polygons con el nombre de su capa real (o cambie el nombre en QGIS a su capa 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

Te invitamos a avalar nuestro ensayo escribiendo un comentario o dejando una puntuación te estamos eternamente agradecidos.

---

---

Utiliza Nuestro Buscador