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)
    (SELECT cs.id, cs.value as val, cs.geometry
         mytable cs 
         INNER JOIN 
             (SELECT DISTINCT value from myTable) v 
             ON (v.value <= cs.value)
     ) AS t
    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.

  • I assume lines 4, 5 6 have id 2, and "originid" is the same as "id".
    – Redoute
    Sep 4, 2015 at 14:40
  • You're right @Redoute, sorry about the typo.
    – Istopopoki
    Sep 4, 2015 at 15:30

1 Answer 1


Try using ST_Union as accumulating window function:

    ST_Union(geometry) OVER (PARTITION BY id ORDER BY value) AS geom
    FROM mytable;
  • That's working perfectly, thank you so much ! No I need to understand the magic behind this, I don't even see the condition on the value. I didn't know about window function, I will have a look. Thanks again.
    – Istopopoki
    Sep 4, 2015 at 15:58
  • hmm the fact that the partition ranges ~by default~ between the first and the current row ~only when~ a order by statement is added is tricky ! inovia.fr/window-functions-basics explains it very well.
    – Istopopoki
    Sep 4, 2015 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.