4

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?

1

1 Answer 1

2

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

input

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

output

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.