# Geolocation Query [closed]

I have two lists within SFMC that both have latitude and longitude values. I need to be able to compare them and find the distance between them in miles, then include or exclude records based on the distance. Does anyone know a query I can use to do this?

## closed as off-topic by glls, Derek F, Mohith Shrivastava, battery.cord, HimanshuApr 8 at 20:20

This question appears to be off-topic. The users who voted to close gave this specific reason:

• "Questions on problems in code you've written must describe the specific problem and include valid code to reproduce it. For help writing short, self-contained syntactically-valid examples, see: SSCCE.org" – glls, Derek F, Mohith Shrivastava, battery.cord, Himanshu
If this question can be reworded to fit the rules in the help center, please edit the question.

## 1 Answer

Here's an example, which is a derivative of the official wiki post on this topic.

If you don't have the X, Y and Z axis data, then you'll need to calculate it first:

``````select
zip
, cos(radians(latitude)) * cos(radians(longitude))  xaxis
, cos(radians(latitude)) * sin(radians(longitude))  yaxis
, sin(radians(latitude))  zaxis
from zip_axis
/* credit https://stackoverflow.com/a/2233208/812377 */

``````

Once you that you can calculate the distances. This example finds the closest store to each subscriber:

``````SELECT
x.emailAddress
, x.firstName
, x.zip
, x.store_zip
, x.store_phone
, x.ranking
, x.distance
from (
SELECT
emailAddress
, firstName
, zip
, store_zip
, store_phone
, row_number() over (
partition by emailAddress
order by emailAddress, distance
) as ranking /* list stores by distance */
, distance
FROM (
SELECT
customer.emailAddress
, customer.firstName
, customer.zip
, store.store_zip
, store.store_phone
, ROUND(3961 * ACOS(
CASE
WHEN (pc1.XAxis * pc2.XAxis + pc1.YAxis *
pc2.YAxis + pc1.ZAxis * pc2.ZAxis) > 1 THEN 1
WHEN (pc1.XAxis * pc2.XAxis + pc1.YAxis *
pc2.YAxis + pc1.ZAxis * pc2.ZAxis) < -1 THEN -1
ELSE (pc1.XAxis * pc2.XAxis + pc1.YAxis *
pc2.YAxis + pc1.ZAxis * pc2.ZAxis)
END),0) distance
FROM [store_customers] customer
INNER JOIN [zip_axis] pc1 ON (pc1.zip = customer.zip)
INNER JOIN [store_locations] store
INNER JOIN [zip_axis] pc2 ON (pc2.zip = store.store_zip)
ON ROUND(3961 * ACOS(
CASE
WHEN (pc1.XAxis * pc2.XAxis + pc1.YAxis *
pc2.YAxis + pc1.ZAxis * pc2.ZAxis) > 1 THEN 1
WHEN (pc1.XAxis * pc2.XAxis + pc1.YAxis *
pc2.YAxis + pc1.ZAxis * pc2.ZAxis) < -1 THEN -1
ELSE (pc1.XAxis * pc2.XAxis + pc1.YAxis *
pc2.YAxis + pc1.ZAxis * pc2.ZAxis)
END),0
) > 0  /* set desired distance range here */
WHERE customer.zip is not null
) rankedRows
) x
where x.ranking = 1
``````