postGIS SQL request with aggregating ST_Union()

Question

I have a postGIS table that looks like this :

id  |  value  | geometry(MultiPolygon)
1      10       ...
1      20       ...
1      30       ...
2      10       ...
2      20       ...
2      30       ...

What I wish to do is to create a new geometry column containing a Multipolygon which is the union of all the other polygons of rows having the same id and a smaller or equal 'value'.

So :

• first row 'new_geometry' would be identical to 'geometry'.
• Second row 'new_geometry' would be the union of the first and second row 'geometry'.
• Third row : union of rows 1,2,3
• equal to row 4
• etc

I have written something like this:

SELECT t.id, t.val, St_Union(t.geometry)
FROM 
    (SELECT cs.id, cs.value as val, cs.geometry
     FROM
         mytable cs 
         INNER JOIN 
             (SELECT DISTINCT value from myTable) v 
             ON (v.value <= cs.value)
     ) AS t
GROUP BY 
    t.val, t.id;

But the geometry column I get is not aggregated, it is equal to the original geometry column. The aggregation work however if I 'GROUP BY' only by one criteria.

Answer 1

Try using ST_Union as accumulating window function:

SELECT
    id,
    value,
    ST_Union(geometry) OVER (PARTITION BY id ORDER BY value) AS geom
    FROM mytable;