Distance minimale PostGIS entre deux grands ensembles de points

Question

J'ai deux tables de points dans PostGIS, disons A et B, et je veux savoir, pour chaque point dans A, quelle est la distance au point le plus proche dans B. Je suis capable de résoudre cela pour de petits ensembles de points avec la requête suivante :

SELECT a.id, MIN(ST_Distance_Sphere(a.geom, b.geom))
FROM table_a a, table_b b
GROUP BY a.id;

Cependant, j'ai quelques millions de points dans chaque table et cette requête s'exécute indéfiniment. Existe-t-il un moyen plus efficace d'aborder cette question ? Je suis ouvert à l'idée d'obtenir une distance approximative plutôt qu'une distance exacte.

Edita: Une légère modification de la réponse fournie par JGH pour renvoyer les distances en mètres plutôt qu'en degrés si les points ne sont pas projetés.

SELECT 
    a.id, nn.id AS id_nn, 
    a.geom, nn.geom_closest, 
    ST_Distance_Sphere(a.geom, nn.geom_closest) AS min_dist
FROM 
    table_a AS a
    CROSS JOIN LATERAL
        (SELECT
            b.id, 
            b.geom AS geom_closest
        FROM table_b b
        ORDER BY a.geom <-> b.geom
        LIMIT 1) AS nn;

Answer 1

Votre requête est lente car elle calcule la distance entre chaque point sans utiliser d'index. Vous pouvez la réécrire pour utiliser l'index <-> qui utilise l'index s'il est utilisé dans l'opérateur order by clause.

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;