# ST_Intersection: Intersection of all geometries in a table

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.

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)

• Well yes, that's working, but how would you do it across all rows without specifying them explicitly? Feb 18, 2018 at 20:06
• It works across all rows, by defining two virtual tables a,b from the same table t1 and go checking over all the rows (a.id<b.id). I didn't understand what you mean by specifying explicitly
– Moh
Feb 18, 2018 at 20:08
• Yes, it's definitely working, thanks! Could you explain what the logic behind the `a.id < b.id`? Feb 18, 2018 at 20:13
• a.id<b.id just not to go over the same row for the intersection (in other words, don't intersect the same geom from the same row)
– Moh
Feb 18, 2018 at 20:15
• Argh, I think I missed something when accepting the answer, sorry. The result of the query will return a list of intersecting geometries. How to apply `ST_Intersection` to it, so I get only one geometry which is an intersection? Feb 18, 2018 at 21:27