1

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, Himanshu Apr 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.

2

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.