PostGIS ST_Intersection – Performing Intersection of All Geometries in a Table

Question

Must be a fairly trivial task, but PostGIS gives me errors..

I have a set of geometries geom in a single table t1, and I would like to find intersection area of all of the rows foo where value is bar.

What I am doing is:

SELECT ST_INTERSECTION(geom) FROM t1 WHERE foo = 'bar' and ST_INTERSECTS(geom)

I understand examples where I have two geometries or two tables, but I can't figure out how to do that in a single table across rows. I have several thousands records to filter, so specifying geometries explicitly is not an option.

---

And yet I am not getting the results I expect from St_intersection (yeah, I assume not the method's fault):

If I apply the method as described above I will get an intersection between each and every row in the table. It's easier to see what I mean, check the picture below.

Instead of having a single geometry (polygon) which represents the shared area between all geometries I get multiple geometries. I am still wondering how to get the area shared by all geometries, not a set of intersections for all pairs in the table.

Answer 1

As I understand that you would like to intersect the geometries across rows in one table, so you need to do something like that:

  select (ST_intersection(a.geom, b.geom)) from t1 a, t1 b where ST_Intersects
    (a.geom,b.geom) AND a.id<b.id And a.foo='bar';

Tested (working)