title: PostGIS recursive intersection between polygons
body: <p>I'm trying to perform a recursive intersection between all the Polygons in a spatial Table, and obtain the resulting (multi)pololygons and the information about every intersection for each of them.</p>
  
  <p>An image (not really in scale) to explain it:
  <img src="https://i.stack.imgur.com/ny477.png" alt="Example"></p>
  
  <p>Let's say there are <code>A, B, C</code> squares in a table. I'd like to have <code>A, B, C, A+B, A+C, B+C, A+B+C</code> polygons in output, and I need to know that <code>A+B</code> is the intersection of <code>A</code> and <code>B</code> and so on.</p>
  
  <p>So far I have a query which performs the intersections, but it does not "cut off" the intersected part of the original polygons. For example:</p>
  
  <pre><code>Polygon A should be      A - (A+B) - (A+C) - (A+B+C)
  Polygon A+C should be    A+C - (A+B+C)
  </code></pre>
  
  <p>An image of the result I get now for the <code>A</code> and <code>A+C</code> polygons:</p>
  
  <p><img src="https://i.stack.imgur.com/4xwkB.png" alt="Current WRONG result"></p>
  
  <p>Here is a test script, using the squares in the images as data. Looking at the <code>area</code> column, it is clear some recursive ST_Difference is missing, I just can't figure out how. Any idea is welcomed.</p>
  
  <pre><code>-- Create a test table
  CREATE TABLE test (
      name text PRIMARY KEY,
      geom geometry(POLYGON)
  );
  
  -- Insert test data
  INSERT INTO test (name, geom) VALUES 
      ('A', ST_GeomFromText('POLYGON((1 2, 1 6, 5 6, 5 2, 1 2))')),
      ('B', ST_GeomFromText('POLYGON((0 0, 0 4, 4 4, 4 0, 0 0))')),
      ('C', ST_GeomFromText('POLYGON((2 0, 2 4, 6 4, 6 0, 2 0))'));
  
  
  -- Query    
  WITH RECURSIVE 
  source (rownum, geom, ret) AS (
      SELECT row_number() OVER (ORDER BY name ASC), ST_Multi(geom), ARRAY[name] FROM test 
  ),
  r (rownum, geom, ret, incroci) AS (
      SELECT rownum, geom, ret, 0 FROM source 
      UNION ALL
      SELECT s.rownum, ST_CollectionExtract(ST_Intersection(s.geom, r.geom), 3), (r.ret || s.ret), (r.incroci + 1) 
          FROM source AS s INNER JOIN r ON s.rownum &gt; r.rownum AND ST_Intersects(s.geom, r.geom) AND ST_Area(ST_Intersection(s.geom, r.geom)) &gt; 0.5
  ),
  result (geom, ret) AS (
      SELECT ST_Union(geom) AS geom, ret FROM r GROUP BY ret
  )
  SELECT geom, ST_Area(geom) AS area, ret FROM result ORDER BY ret
  </code></pre>
  
  <p>The window function isn't strictly necessary in this particular example of course, but this code is a simplified version of my real case, which does a few more things on the side.</p>
  
  <p>I'm using PostgreSQL 9.2 and PostGIS 2.0</p>