1

I have the field_1 of my Table_1 that must have the minimum distance between the geometry of my Table_1 and any geometry of my Table_2. To do so, I used a CROSS JOIN (Full Cartesian Product) between Table_1 and Table_2. I also used the KNN operator <->.

My trouble is that it took more than 3 hours to run and I decided to abort the query while it was running.

Table_1 has 56230 entities.

Table_2 has 234843 entities.

Here is my code:

UPDATE Table_1
SET field_1 = list.distance
FROM(
    SELECT T1.gid AS name, MIN(T1.geom <-> T2.geom) AS distance
    FROM Table_1 AS T1
    CROSS JOIN Table_2 AS T2
    GROUP BY T1.gid
    ORDER BY T1.gid
     ) AS list
WHERE Table_1.gid = list.name;

Do you have any ideas with a lighter query that could execute this much faster ? I already have created spatial indices.

Vince
  • 20,017
  • 15
  • 45
  • 64
Basile
  • 617
  • 4
  • 15

1 Answers1

4

the <-> operator will use the index only if it is within the order by clause.

You can rewrite your query

select a.id,closest_pt.id, closest_pt.dist
from tablea a
CROSS JOIN LATERAL
  (SELECT
     id , 
     a.geom <-> b.geom as dist
     FROM tableb b
     ORDER BY a.geom <-> b.geom
   LIMIT 1) AS closest_pt;
JGH
  • 41,794
  • 3
  • 43
  • 89
  • Your query is very efficient. But when I change it to update my field_1, the time taken is very long. It seems that updating is not as efficient as selecting. Any idea to lower the computing time ? – Basile Jul 23 '19 at 08:33
  • Looks like it is repeating the first query the number of entities in the table_1. Can't it be done for once ? – Basile Jul 23 '19 at 08:45