TransWikia.com

Translate aggregate into SQL function

Geographic Information Systems Asked on November 1, 2021

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

enter image description here

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)

2 Answers

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

Answered by Gabriel De Luca on November 1, 2021

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

result

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

reulst_2

Answered by Taras on November 1, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP