question

tlenzmeier avatar image
tlenzmeier asked

Calculating Distance Between Locations With Multiple To/From

Hello,

I am trying to calculate the distance between two addresses. In one table I have a list of members and in the second table, I have a list of store locations. I have the Haversine formula, but I'm not sure how to go about calculating distances between one address and thousands of locations

sql server 2016calculationsbetween
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Could you provide some example table structures and data?

0 Likes 0 ·

There are two tables: customers and locations. Each table has a street address, city, state, and postal code. It also has latitude and longitude. The customer table has approximately 15 thousand records and the locations table has approximately 5 thousand records. For each customer, I need to be able to calculate the distance in miles for each location. The purpose of this is to identify customers with specific distances from each store. There is nothing to join one table to the other, other than the country. I hope this helps!

0 Likes 0 ·

Ultimately, I need to get a count of records where the distances are 0-3, 3-5, 5-10, and greater than 10.

0 Likes 0 ·

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

This may help. I'm simply cross joining between 2 tables to get all possibilities, and using a function to calculate the distances.

You can use the Haversine formula (I searched and found this one)

declare @customers table (customer_id int, customer varchar(50), latitude float, longitude float)
insert into @customers select 1, 'Kev', 53.652740, -2.632410
insert into @customers select 2, 'Dave', 53.576866, -2.428219

declare @locations table (location_id int, location varchar(50), latitude float, longitude float)
insert into @locations select 1, 'Preston Store', 53.757729, -2.703440
insert into @locations select 2, 'Manchester Store', 53.480759, -2.242631

select
	customers.customer, locations.location, 
	( 3959 * acos( cos( radians(customers.latitude) ) * cos( radians( locations.latitude ) ) 
		* cos( radians(locations.longitude) - radians(customers.longitude) ) + sin( radians(customers.latitude) ) 
		* sin( radians(locations.latitude)))) AS distance 
from
	@customers customers cross join @locations locations


customer              location                  distance
--------------------- ------------------------- ----------------------
Kev                   Preston Store             7.81460021901157
Dave                  Preston Store             16.8263824798027
Kev                   Manchester Store          19.9262420100164
Dave                  Manchester Store          10.1096063371716

(4 rows affected)



or use the geography data types built into SQL

declare @customers table (customer_id int, customer varchar(50), latitude float, longitude float)
insert into @customers select 1, 'Kev', 53.652740, -2.632410
insert into @customers select 2, 'Dave', 53.576866, -2.428219

declare @locations table (location_id int, location varchar(50), latitude float, longitude float)
insert into @locations select 1, 'Preston Store', 53.757729, -2.703440
insert into @locations select 2, 'Manchester Store', 53.480759, -2.242631

select
	a.customer, a.location, 
	a.CustPoint.STDistance(a.LocPoint) * 0.000621371 as [Distance in Miles]
from
(
select
	customers.customer, locations.location, 
	geography::Point(customers.latitude, customers.longitude, 4326) as CustPoint,
	geography::Point(locations.latitude, locations.longitude, 4326) as LocPoint
from
	@customers customers cross join @locations locations
	) a



customer              location                  Distance in Miles
--------------------- ------------------------- ----------------------
Kev                   Preston Store             7.82402021340711
Dave                  Preston Store             16.8589314265032
Kev                   Manchester Store          19.9738397541548
Dave                  Manchester Store          10.1318917831147

(4 rows affected)

2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You're a lifesaver! Now if I run this, I'll get 75 million rows. Since I want to count the number of customers based on mileage distance (0-3, 3-5, 5-10, 10+), can I simply write a case statement and then count the customers?

0 Likes 0 ·

Yes, something like

select 
	CustomerStoreMiles.location,
	sum(case when CustomerStoreMiles.[Distance in Miles] <=3 then 1 else 0 end) as [0-3 Miles],
	sum(case when CustomerStoreMiles.[Distance in Miles] >3 and CustomerStoreMiles.[Distance in Miles] <=5 then 1 else 0 end) as [3-5 Miles],
	sum(case when CustomerStoreMiles.[Distance in Miles] >5 and CustomerStoreMiles.[Distance in Miles] <=10 then 1 else 0 end) as [5-10 Miles],
	sum(case when CustomerStoreMiles.[Distance in Miles] >10 then 1 else 0 end) as [10+ Miles]
from (
	select
		a.customer, a.location, 
		a.CustPoint.STDistance(a.LocPoint) * 0.000621371 as [Distance in Miles]
	from
	(
	select
		customers.customer, locations.location, 
		geography::Point(customers.latitude, customers.longitude, 4326) as CustPoint,
		geography::Point(locations.latitude, locations.longitude, 4326) as LocPoint
	from
		@customers customers cross join @locations locations
		) a
	) CustomerStoreMiles
group by CustomerStoreMiles.location

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.