Translate aggregate into SQL function

Question

In this question I was asking for a aggregate solution to update a attribute (ID) if I change or add an new feature and got a great answer from OGmaps:
Add and change attribute / feature cascaded (QGIS)
This is simple and perfectly working! But I found out now, that this is consuming too much resources if I have more then 300 polygons. So I need a other way and thought about SQL - I'm sure this can do the same job maybe with more performance. So how do I translate this aggregate function:
aggregate(
  layer:= 'Polygons',
  aggregate:= 'concatenate',
  expression:= Id,
  concatenator:= '-',
  filter:= contains($geometry, geometry(@parent)),
  order_by:= -area($geometry) 
)

Into a SQL-Expression?

Here I uploaded a example data set: https://www.map-site.de/tmp/sql-cascade.zip I need the green dynamic ID's (with place holder).

The green ID's are generated by connected aggregate functions in a virtual field. If any object name is changed, the ID will update - very nice but too slow for a good workflow (I expect up to 1500 polygons)

Gabriel De Luca · Accepted Answer

Just to offer an improved performance expression, so you have something to compare against.
Instead of the buffer and the contains predicate, you can check if the centroid of the geometry of the source feature intersects with other geometries and the area of the source geometry is less or equal than the current geometry one. Also, there is no need to compute ellipsoidal area. And you can build the array just one time:
with_variable(
  'my_array',

aggregate(
    layer:= 'polygons',
    aggregate:= 'array_agg',
    expression:= "code",
    concatenator:= '-',
    filter:= 
      intersects(
        centroid(geometry(@parent)),
        $geometry)
      AND
      area(geometry(@parent)) <= area($geometry),
    order_by:= -area($geometry), 
  ),

concat(
    lpad(
      array_get(
        @my_array,
        0),
      3,
      '0'),
    coalesce(
      array_get(
        @my_array,
        1),
      'X'),
    coalesce(
      lpad(
        array_get(
          @my_array,
          2),
        2,
        '0'),
      '00')
  )
)

Taras · Answer

Maybe the SQL logic and statements are not perfect but it solves the task. Unfortunately till now I was not able to preserve the required order of attributes.

-- calculating the number of geometries that lay inside of the current geometry
-- (upper level)
WITH geom_cont AS (
    SELECT p1.*, SUM(st_contains(p1.geometry, p2.geometry)) - 1 AS num1
    -- "-1" is required to exclude the self-containing
    -- The same result can be also achieved with
    -- SUM(st_contains(p1.geometry, p2.geometry) AND NOT st_equals(p1.geometry, p2.geometry)) AS num1
    FROM polygons AS p1
    CROSS JOIN polygons AS p2 ON st_contains(p1.geometry, p2.geometry)
    GROUP BY p1.fid
),

-- calculating the number of geometries that hold the current geometry
-- (lower level)
geom_with AS (
    SELECT p2.*, SUM(st_within(p2.geometry, p1.geometry)) - 1 AS num2
    -- "-1" is required to exclude the self-within
    -- The same result can be also achieved with
    -- SUM(st_within(p2.geometry, p1.geometry) AND NOT st_equals(p1.geometry, p2.geometry)) AS num2
    FROM polygons AS p2
    CROSS JOIN polygons AS p1 ON st_within(p2.geometry, p1.geometry)
    GROUP BY p2.fid
)

-- main query
SELECT p.*, num1, num2,
    (CASE
        WHEN num1 = 0 AND num2 != 0 THEN GROUP_CONCAT(DISTINCT p3.code)
        WHEN num1 != 0 AND num2 = 0 THEN p3.code
        ELSE GROUP_CONCAT(DISTINCT p4.code)
    END) AS ID
FROM polygons AS p
JOIN geom_cont ON geom_cont.fid = p.fid
JOIN geom_with ON geom_with.fid = p.fid
CROSS JOIN polygons AS p3 ON st_intersects(p.geometry, p3.geometry)
CROSS JOIN polygons AS p4 ON st_contains(p4.geometry, p.geometry)
GROUP BY p.fid

P.S. Works also with single geometries as well as with geometries nested more than 3 times, see image below.

Translate aggregate into SQL function

2 Answers

Add your own answers!

Ask a Question