2

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.

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

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;
  • 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 '15 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 '15 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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