How can we help you today? How can we help you today?

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

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?

Adamsymonds
0

Comments

1 comment

Sort by
  • 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.
    Dan_J
    0

Add comment

Please sign in to leave a comment.