Search code examples
sqlsql-servert-sqlrankingrow-number

SQL Server : rank data based on minimum distance

I have two tables with cities, like the following:

Table 1

Place.1 | Latitude.1 | Longitude.1

Table 2

Place.2 | Latitude.2 | Longitude.2

Many places in the two tables are the same, but I cannot join them based on the Place name, since they are in different languages, so I was thinking of joining based on minimum distance.

There is a way to calculate the great circle distance between two points on a sphere (I have already implemented this function). Any ideas how I could find the closest place on Table 2 for every place on Table 1??

Thank you very much for any help!

Alex

Solution

  • You can use cross apply:

    select t1.*, t2.*
from table1 t1 cross apply
     (select top 1 t2.*
      from table2 t2
      order by distance(t1, t2)  -- put your appropriate function here
     ) t2;