In SQL, what is the most efficient way to find nearest pairs of points for two tables.

Adamsymonds

Hello,

After completing the salesforce course online and I am learning about SQL and I am stuck somewhere between the nearest pairs of two tables.

My query is-

let's say I have tableA.points which has a bunch of geographic points.

and I have tableB.points which has a bunch of geographic points, as well.

I want to compare every row  tableA.points to every row in tableB.points, and list the closest (shortest distance) point for each row in tableA.points.

so, actually two points:

1. the only way I can think of is a cartesian product (cross join) or an aggregate correlated subquery.
2. the issue with the correlated subquery is that I can't attach tableB.Identifer it to the outer query.

for example:

SELECT
  tableA.*
  ,(SELECT
      CONCAT(tableB.IDENTIFIER,':',MIN(ST_DISTANCE(tableA.points, tableB.points))
    FROM tableB) as closest_point
FROM tableA

The big problem here is that I have over a million rows for each of these tables...

Any advice?

Dan_J

Hi @Adamsymonds

Thanks for reaching out to us regarding this.

I'm afraid that what you are describing it not something any of our products are able to do.

It also appears that you may be looking for more generic programming advice, which unfortunately is not something we are able to help with.

I'm very sorry we can't be of more help with this.