I have two sets of polygons in two tables. The sets overlap each other. For each polygon in set A, I would like to get the ID of the polygon in set B that it overlaps the most. I'm using PostgreSQL with the PostGIS extension.

I know just enough about SQL to know that you can only join based on true/false conditions. So this won't work:

  a.id as a_id,
  b.id as b_id,
  max(ST_Area(ST_Intersection(a.geom, b.geom)))

because max() can't be in the ON clause.

ST_Intersects() is a true/false test, so I could join on that, but polygons in set A will often overlap with more than one polygon in set B, and I need to know which one overlaps the most. ST_Intersects would presumably just return the first overlapping ID it came across, regardless of the extent of the overlap.

This seems like it should be do-able, but it's beyond me. Any thoughts?

1 Answer 1


You could use something like:

  a.id as a_id,
  b.id as b_id,
  ST_Area(ST_Intersection(a.geom, b.geom)) as intersect_area
FROM a, b
ORDER BY a.id, ST_Area(ST_Intersection(a.geom, b.geom)) DESC


1) Calculates ST_Area(ST_Intersection(a.geom, b.geom)) for every (a,b) pair of records.

2) Orders them by a.id and by intersect_area when a.id are equal.

3) In every group of equal a.id it picks the firs record (the first record has the highest intersect_area because of ordering on step 2).

  • That solves the problem very neatly. Thank you thank you! DISTINCT ON is new to me -- very handy in this context. Commented Nov 19, 2012 at 6:37
  • 2
    When I try this polygons in A that does not overlap with anyone in B still gets an b.id and a 0 intersections area. This can be avoided with WHERE st_intersects(a.geom,b.geom)
    – Bera
    Commented May 15, 2020 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.