0

I have 2 database with
- the geographical address of users (latitude and longitude)
- the geographical address of my shops (latitude and longitude)

I want to display in an email the 3 nearest shops from each user.

I thought of two solutions:
- merge the 2 table to have a 3rd one looking like
USER | SHOPS_NEARBY_1| SHOPS_NEARBY_2| SHOPS_NEARBY_3
or
USER | SHOPS | DISTANCE_USER_SHOPS
- use an ampscript formula directly in an email to show the shops with distance < x

I am afraid I will have way too much lines in my first solution and I am not sure that the formulas allowed for ampscript will be able to calcul a distance
What do you think would be the best solution? Is there another way?

  • are latitude and longitude in same field or separate fields? – Gortonington Apr 10 '18 at 12:26
  • separate fields – Emilie Apr 10 '18 at 15:10
  • you could likely use the subtract function on these if you format them as decimals to get distance between locations - but you would need to work on a solution for comparing negative lat/long to positive lat/long. Likely something with indexof and if statements. – Gortonington Apr 10 '18 at 16:28
  • Do you know the formulas I should use? I thought I would need cosinus and so on – Emilie Apr 11 '18 at 7:59
0

You should be able to do this with subtraction/addition. I would not use this as a drop dead accurate approach, but for a simple comparison this should be accurate enough.

The easiest is if you have the 'Signed degrees' format (41.2500) From here you have a range of -180 to 180 - but each is a decimal format value. It is a little trickier than just simple subtraction, but not as difficult as others may think.

Making the Calculation

Here’s where your math is put to test. If both locations are on the same side of the equator, then you must deduct the smaller figure from the larger. If they are on opposite sides of the equator, then you must add the two figures together. Forget about any minus signs that you may see -- they just signify that the figure is the number of degrees to the south of the equator.

Example AMPScript:

%%[

    SET @Long = AttributeValue(yourLongitude)
    SET @StoreLong = AttributeValue(yourStoreLong)

    /* YOU MAY NEED TO CONVERT TO DECIMAL FORMAT IF STORED AS TEXT */

    IF @Long > @StoreLong THEN

       SET @First = @Long
       SET @Second = @StoreLong

    ELSE   

       SET @First = @StoreLong
       SET @Second = @Long

    ENDIF

    IF INDEXOF(@Long, "-") > 0 OR INDEXOF(@StoreLong, "-") > 0 THEN

       SET @longDistance = ADD(@First, @Second)

    ELSE

      SET @longDistance = SUBTRACT(@First, @Second)

    ENDIF

]%%



The tricky part comes in if your Lat/Long is in any other format (something like: 41°25'01"N). You would then need to convert this into 'Signed degrees' format to utilize the above. (ref)

To convert the Latitude or Longitude to Map Coordinates (MC):

Step 1: Multiply (×) the "degrees" by 60

Step 2: Add (+) the "minutes"

Step 3: If the Latitude (Longitude) degrees are S (W) use a minus sign ("-") in front. This result is the Latitude (Longitude) converted to Minutes.

Latitude Map Coordinates:

Step 1: Degrees × 60 = 42 × 60 = 2520

Step 2: 2520 + 20.736 = 2540.736

Step 3: The Latitude is not "S" so the Latitude converted to Minutes is 2540.736

Longitude Map Coordinates:

Step 1: Degrees × 60 = 71 × 60 = 4260

Step 2: 4260 + 5.745 = 4265.745

Step 3: The Longitude is "W" so the answer is -4265.745

  • So, if I use longitude only would it be sufficient to have an idea of the closest store? Or should I add the longitude distance and the latitude one? – Emilie Apr 11 '18 at 13:49
  • I would definitely use both numbers - perhaps even make it an average. As someone could be in New Jersey(USA) and another in Turkey and they would have very similar latitude, but hugely different longitude. – Gortonington Apr 11 '18 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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