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:


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? – mikitk Feb 18 '18 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 '18 at 20:08
  • Yes, it's definitely working, thanks! Could you explain what the logic behind the a.id < b.id? – mikitk Feb 18 '18 at 20:13
  • 1
    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 '18 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? – mikitk Feb 18 '18 at 21:27

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.