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