Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

SELECT as a_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?

share|improve this question

migrated from Nov 19 '12 at 14:41

This question came from our site for professional and enthusiast programmers.

up vote 6 down vote accepted

You could use something like:

SELECT DISTINCT ON ( as a_id, as b_id,
  ST_Area(ST_Intersection(a.geom, b.geom)) as intersect_area
FROM a, b
ORDER BY, 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 and by intersect_area when are equal.

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

share|improve this answer
That solves the problem very neatly. Thank you thank you! DISTINCT ON is new to me -- very handy in this context. – Hugh Stimson Nov 19 '12 at 6:37

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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