4

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.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
mikitk
  • 167
  • 1
  • 9

1 Answers1

12

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)

Moh
  • 1,588
  • 1
  • 9
  • 17
  • 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
  • You missed something!,ok it's easy to add it, However, I will update my answer – Moh Feb 18 '18 at 21:43