Dołącz na podstawie maksymalnego nakładania się w PostGIS / PostGresQL?

Mam dwa zestawy wielokątów w dwóch tabelach. Zestawy nakładają się na siebie. Dla każdego wielokąta w zestawie A chciałbym uzyskać identyfikator wielokąta w zestawie B, który najbardziej na siebie zachodzi. Używam PostgreSQL z rozszerzeniem PostGIS.

Wiem wystarczająco dużo o SQL, aby wiedzieć, że możesz dołączyć tylko na podstawie warunków prawda / fałsz. Więc to nie zadziała:

SELECT
  a.id as a_id,
  b.id as b_id,
FROM
  a
JOIN
  b
ON
  max(ST_Area(ST_Intersection(a.geom, b.geom)))

ponieważ max () nie może być w klauzuli ON.

ST_Intersects()jest testem prawda / fałsz, więc mógłbym się przyłączyć, ale wielokąty w zestawie A często nakładają się z więcej niż jednym wielokątem w zestawie B i muszę wiedzieć, który z nich najbardziej się pokrywa . ST_Intersects prawdopodobnie zwróciłby pierwszy zachodzący na siebie identyfikator, na który natknął się, bez względu na zakres nakładania się.

Wydaje się, że powinno być to wykonalne, ale jest poza mną. jakieś pomysły?

Możesz użyć czegoś takiego jak:

SELECT DISTINCT ON (a.id)
  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

To:

1) Oblicza ST_Area (ST_Intersection (a.geom, b.geom)) dla każdej (a, b) pary rekordów.

2) Sortuje je według a.id i intersect_area, gdy a.id są równe.

3) W każdej grupie równej a. Id wybiera rekord pierwszego (pierwszy rekord ma najwyższy intersect_area z powodu uporządkowania w kroku 2).