0

I've 2 sets of polygons in 2 tables and the sets overlap each other. For each polygon in set A, I get the ID of the polygon in set B that it overlaps the most.

This is the query:

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

It works properly so, what's the problem? It's very slow (it takes several minutes). Table "a" has more than 10,000 rows and table "b" 700 rows. So, how can I improve the perfomance? I've proved with spatial index but I haven't had better results.

New contributor
Manu is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Browse other questions tagged or ask your own question.