Splitting Multipart geometries using Virtual Layer in QGIS

Question

I have a Virtual layer already set up and working, the only thing that could make it better is getting rid of multipart geometries. The query is:

SELECT
    ejido, circ, radio, mzna, cc, ten, registrado,  
    RANK() OVER (ORDER BY CalcRegistrado(ten, registrado)) AS color,
    st_buffer(st_union(geometry), 0.1) AS geometry  
FROM
    "VIRTUAL_ORIGEN_PARCELAS" 
WHERE
    registrado IS NOT NULL
    AND registrado <> ''  
GROUP BY
    circ, radio, cc, ten <> 'S', mzna, CalcRegistrado(ten,registrado)

The buffer is meant to join polygons because I have to work with spaghetti layers, but sometimes parcels are indeed separated by other parcels and I need them to be separate polygons in the resulting layer. I have tried to generate a numbers table and selecting from a join with my geometry result (as seen here) but with no success.

To do so, first create a table containing the numbers from 1 to 100,000 (which should be plenty to accommodate the number of individual elements in a multielement geometry), as follows:
SELECT TOP 100000 IDENTITY(int,1,1) AS n  
INTO Numbers  
FROM MASTER..spt_values a, MASTER..spt_values b; 
You can then join from your spatial table to the numbers table to retrieve each individual geometry using a query as follows:
SELECT  
  id,  
  geom.STGeometryN(Numbers.n) 
FROM  
  #MixedData JOIN Numbers
  ON Numbers.n <= geom.STNumGeometries()

How can I apply that to my query?

Some inspiration can be found here: https://gis.stackexchange.com/a/382382/99589 — Taras, Oct 18 '21 at 08:20

score 2 · Answer 1 · answered Oct 18 '21 at 08:58

Let's assume there is polygon layer called 'test_for_multi' with two multigeometry features in it, see image below.

Using the following query, it is possible to get rid of multipart geometries.

-- generating couples of attributes for each feature (id : parts)
WITH RECURSIVE multi_to_single(id, part) AS (
    SELECT
        conf.id, conf.start
    FROM
        conf
    UNION ALL
    SELECT
        conf.id, multi_to_single.part + conf.step
    FROM
        multi_to_single, conf
    WHERE
        multi_to_single.id = conf.id
        AND multi_to_single.part + conf.step <= conf.stop
    ),
-- configurations for layer
layer AS (
    SELECT
        *
    FROM
        "test_for_multi" -- here chenge "test_for_multi" into your layer's name
    ),
-- configurations for parts
conf AS (
    SELECT
        layer.id,
        1 AS start,
        1 AS step,
        st_numgeometries(layer.geometry) AS stop
    FROM
        layer
    )
-- main query
SELECT
    layer.id AS id,
    multi_to_single.part AS part,
    ST_GeometryN(layer.geometry, multi_to_single.part) AS geom
FROM
    layer, multi_to_single
WHERE
    multi_to_single.id = layer.id

Note: make sure to change the "test_for_multi" in layer into your layer name with multigeometries.

The output polygon layer with its attribute table will look like

Splitting Multipart geometries using Virtual Layer in QGIS

1 Answers1