What are the challenges you face when working across database platforms? Take the survey
Options

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?

Tagged:

Answers

  • Options
    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.
    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
Sign In or Register to comment.