title: PostGIS minimum distance between two large sets of points
body: <p>I have two tables of points in PostGIS, say A and B, and I want to know, for every point in A, what is the distance to the closest point in B. I am able to solve this for small sets of points with the following query:</p>
  
  <pre><code>SELECT a.id, MIN(ST_Distance_Sphere(a.geom, b.geom))
  FROM table_a a, table_b b
  GROUP BY a.id;
  </code></pre>
  
  <p>However, I have a couple million points in each table and this query runs indefinitely. Is there some more efficient way to approach this. I am open to getting an approximate distance rather than an exact one.</p>
  
  <p><strong>Edit:</strong> A slight modification to the answer provided by JGH to return distances in meters rather than degrees if points are unprojected.</p>
  
  <pre><code>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 &lt;-&gt; b.geom
          LIMIT 1) AS nn;
  </code></pre>