CODEWITHSUNDEEP
sqlsql-server
user5342687
user5342687

Reputation: 55

Get closest store for each customer

I have two tables, one is a list of store locations (with lat/long), and the other is a customer list (with address lat/long).

What I need is a query that calculate closest store for each customer?

Upvotes: 1

Views: 331

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522050

The answer to your question is something called the Haversine formula, and you can about it here, which gives actual SQL code which should run in SQL Server.

You need to take the CROSS JOIN between the two tables, which will give you every pair of customer address and store location. You need to consider each one of these pairs because you don't know which one will be the nearest neighbor a priori.

For each pair, you can use this SQL code to get the distance between the customer and store:

111.045*DEGREES(ACOS(COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
                     COS(RADIANS(long1) - RADIANS(long2)) +
                     SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)))) AS distance_in_km

Here (lat1, long1) is the latitude and longitude of a customer and (lat2, long2) are the values for a certain store.

Upvotes: 1

Related Questions