Calculating Distance Between Locations With Multiple To/From

Question

question

tlenzmeier asked Aug 7, '19

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 2016 calculations between

3

10 |1200

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

Kev Riley ♦♦ commented · Aug 07, 2019 at 01:37 PM

Could you provide some example table structures and data?

0 ·

tlenzmeier commented · Aug 07, 2019 at 01:56 PM

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 ·

tlenzmeier commented · Aug 07, 2019 at 02:01 PM

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

0 ·

Answer 1 · 2019-08-07T14:16:24Z

Kev Riley answered Aug 7, '19

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

10 |1200

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

tlenzmeier commented · Aug 07, 2019 at 04:25 PM

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 ·

Kev Riley ♦♦ tlenzmeier commented · Aug 08, 2019 at 06:30 AM

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 ·

question