cancel
Showing results for
Did you mean:
Helper I

## Distance Between List of Coordinates

Hi y'all, I'm trying to find the distance between a list of coordinates in sequence, i.e. distance(x , x+1), distance(x+1, x+2), ... so that at the end of the day, we can add them to find the total distance.

I've tried many ForAlls, Sequences, and Collections, but I can't seem to get this to work.

The table below is a day of travel for Bob Ross:

Bob Ross

 ID Location Time Coordinates 63 HQ 8:00 AM 32.6894,-97.2937 64 Location 1 9:00 AM 29.7604,-95.3698 69 Location 1 10:30 AM 29.7604,-95.3698 72 Location 2 11:30 AM 30.2672,-97.7431 79 Location 2 1:00 PM 30.2672,-97.7431 81 Lunch Start 1:30 PM 30.2864,-97.7425 89 Lunch End 2:30 PM 30.2864,-97.7425 96 Gas 3:00 PM 30.1075,-97.3058 102 HQ 4:00 PM 32.6894,-97.2937

As you can see, ID isn't consecutive because many users will be updating their location throughout the day.

This seems like a pretty simple problem in traditional coding, but I can't find a way to solve this in PowerApps. This is the very last thing I need to solve. Any help is appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

Hi @natrob :

Use the BingMap connector to easily obtain the distance between two coordinates.I've made a test for your reference:

1\My data source:

``````ClearCollect(
'Bob Ross',
{ID:63,Location:"HQ",Time:"8:00 AM",Coordinates:"32.6894,-97.2937"},
{ID:64,Location:"Location 1",Time:"9:00 AM",Coordinates:"29.7604,-95.3698"},
{ID:69,Location:"Location 1",Time:"10:30 AM",Coordinates:"29.7604,-95.3698"},
{ID:72,Location:"Location 2",Time:"11:30 AM",Coordinates:"30.2672,-97.7431"},
{ID:79,Location:"Location 2",Time:"1:00 PM",Coordinates:"30.2672,-97.7431"},
{ID:81,Location:"Lunch Start",Time:"1:30 PM",Coordinates:"30.2864,-97.7425"},
{ID:89,Location:"Lunch End",Time:"2:30 PM",Coordinates:"30.2864,-97.7425"},
{ID:96,Location:"Gas",Time:"3:00 PM",Coordinates:"30.1075,-97.3058"},
{ID:102,Location:"HQ",Time:"4:00 PM",Coordinates:"32.6894,-97.2937"}
)``````

2\Add a button and set it's OnSelect porperty to:

``````Set(
TotalDistance, /*TotalDistance is my custom variable*/
Sum(
ForAll(
Sequence(CountRows('Bob Ross') - 1),
{
distance: BingMaps.GetRoute(
Last(FirstN('Bob Ross',Value)).Coordinates,
Last(FirstN('Bob Ross',Value + 1)).Coordinates
).travelDistance
}
),
distance
)
)``````

3\Add a label and set it's Text property to:

``TotalDistance & "Kilometer"``

The Result:

# Bing Maps (Preview)

Best Regards,

Bof

8 REPLIES 8
Most Valuable Professional

Hi @natrob ,

This is not simple, but fortunately I have crews finding power poles, so I will give you a good start on this.

Assuming you are dealing with a gallery above called Gallery1 (only because that is what I just tested it on), the distance from your current location to the item selected in the gallery would be

``````With(
{
wLat1:
Left(
Gallery1.Selected.Coordinates,
Find(
",",
Gallery1.Selected.Coordinates
)-1
),
wLong1:
Right(
Gallery1.Selected.Coordinates,
Len(Gallery1.Selected.LatLong) -
Find(
",",
Gallery1.Selected.Coordinates
)
),
wLat2: Location.Latitude,
wLong2: Location.Longitude
},
12742 *
Asin(
Sqrt(
0.5 -
Cos(
(wLat1 - wLat2) * 0.0174532925199432
)/2 +
Cos(
wLat1 * 0.0174532925199432
) *
Cos(
wLat1 * 0.0174532925199432
) *
(
1 -
Cos(
(wLong1 - wLong2) * 0.0174532925199432
)
) / 2
)
)
)``````

You can then do the same exercise for the second set. If you had lat and long in separate fields (as I do) the code at the top is far less complex.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Community Support

Hi @natrob :

Use the BingMap connector to easily obtain the distance between two coordinates.I've made a test for your reference:

1\My data source:

``````ClearCollect(
'Bob Ross',
{ID:63,Location:"HQ",Time:"8:00 AM",Coordinates:"32.6894,-97.2937"},
{ID:64,Location:"Location 1",Time:"9:00 AM",Coordinates:"29.7604,-95.3698"},
{ID:69,Location:"Location 1",Time:"10:30 AM",Coordinates:"29.7604,-95.3698"},
{ID:72,Location:"Location 2",Time:"11:30 AM",Coordinates:"30.2672,-97.7431"},
{ID:79,Location:"Location 2",Time:"1:00 PM",Coordinates:"30.2672,-97.7431"},
{ID:81,Location:"Lunch Start",Time:"1:30 PM",Coordinates:"30.2864,-97.7425"},
{ID:89,Location:"Lunch End",Time:"2:30 PM",Coordinates:"30.2864,-97.7425"},
{ID:96,Location:"Gas",Time:"3:00 PM",Coordinates:"30.1075,-97.3058"},
{ID:102,Location:"HQ",Time:"4:00 PM",Coordinates:"32.6894,-97.2937"}
)``````

2\Add a button and set it's OnSelect porperty to:

``````Set(
TotalDistance, /*TotalDistance is my custom variable*/
Sum(
ForAll(
Sequence(CountRows('Bob Ross') - 1),
{
distance: BingMaps.GetRoute(
Last(FirstN('Bob Ross',Value)).Coordinates,
Last(FirstN('Bob Ross',Value + 1)).Coordinates
).travelDistance
}
),
distance
)
)``````

3\Add a label and set it's Text property to:

``TotalDistance & "Kilometer"``

The Result:

# Bing Maps (Preview)

Best Regards,

Bof

Most Valuable Professional

Thanks @v-bofeng-msft ,

I was reluctant to go down that track due to past experience on mobile devices in particular - I dragged out an old test app and get this randomly on iPads - seems to work OK in browser however. Also going through getting an API Key needs a bit of explaining.

Community Support

Hi @WarrenBelz :

Thanks for your suggestion. To some extent, your solution may indeed be more scientific. I have looked for similar solutions before but no results. Thank you very much for your contribution.

Best Regards,

Bof

Helper I

Wow! This is exactly what I needed! Thank you very much!

Helper I

Hi @WarrenBelz , thanks for spending time answering my question! I was quite unclear with my question as I intended to use BingMaps.GetRoute() for road distances rather than distances between coordinates, so apologies for that. I appreciate you answering and even amending code to fit my dataset

Helper I

Hi WarrenBelz

Have you been able to solve this, so this does not happen? I get the same error

Most Valuable Professional

Hi @JHS2019 ,

I have progressed a bit, but still only use Bing maps for a single calculation of road distance between to lat/long points. It still falls over at times and will not do multiple (as in a gallery) calculation without a big red error. I mainly use Google maps for locaiton pins and an absolute straight line calculation based on lat/long.

Announcements

#### Copilot Cookbook Challenge | WINNERS ANNOUNCED | Win Tickets to the Power Platform Conference

We are excited to announce the "The Copilot Cookbook Community Challenge random winners have been selected for the Challenge.  Thank you to everyone who participated in this challenge.    Copilot Cookbook Gallery:Power Apps Cookbook Gallery: 1. @swaminawale  1. @renatoromao     2.  @SpongYe  2.   @nickpotts10  *Please note if for any reason a winner declines, we will have another random drawing.   Check out all of the Cookbook Submissions: 1. Copilot Studio Cookbook Gallery: https://aka.ms/CS_Copilot_Cookbook_Challenge 2. Power Apps Copilot Cookbook Gallery: https://aka.ms/PA_Copilot_Cookbook_Challenge   There will be 5 chances to qualify for the final drawing: Early Bird Entries: March 1 - June 2Week 1: June 3 - June 9Week 2: June 10 - June 16Week 3: June 17 - June 23Week 4: June 24 - June 30WINNERS ANNOUNCED - JULY 8th     At the end of each week, we will draw 5 random names from every user who has posted a qualifying Copilot Studio template, sample or demo in the Copilot Studio Cookbook or a qualifying Power Apps Copilot sample or demo in the Power Apps Copilot Cookbook. Users who are not drawn in a given week will be added to the pool for the next week. Users can qualify more than once, but no more than once per week. Four winners will be drawn at random from the total qualifying entrants. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once. If they are drawn multiple times, another user will be drawn at random. Prizes:  One Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 (\$1800 value, does not include travel, lodging, or any other expenses) Winners are also eligible to do a 10-minute presentation of their demo or solution in a community solutions showcase at the event. To qualify for the drawing, templates, samples or demos must be related to Copilot Studio or a Copilot feature of Power Apps, Power Automate, or Power Pages, and must demonstrate or solve a complete unique and useful business or technical problem. Power Automate and Power Pagers posts should be added to the Power Apps Cookbook. Final determination of qualifying entries is at the sole discretion of Microsoft. Weekly updates and the Final random winners will be posted in the News & Announcements section in the communities on July 29th, 2024. Did you submit entries early?  Early Bird Entries March 1 - June 2:  If you posted something in the "early bird" time frame complete this form: https://aka.ms/Copilot_Challenge_EarlyBirds if you would like to be entered in the challenge. Early Bird Submissions: @renato Week 1 Results:  Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Copilot Cookbook Gallery:Power Apps Cookbook Gallery:1.  @Mathieu_Paris 1.   @SpongYe 2.  n/a2.   @Deenuji 3.  n/a3.   @Nived_Nambiar  4.  n/a4.   @ManishSolanki 5.  n/a5.    n/a   Week 2 Results:  Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Copilot Cookbook Gallery:Power Apps Cookbook Gallery:1. Kasun_Pathirana1. ManishSolanki2. cloudatica2. madlad3. n/a3. SpongYe4. n/a4. n/a5. n/a5. n/a     Week 3 Results:  Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Copilot Cookbook Gallery:Power Apps Cookbook Gallery:1. Parul_Yadav_Neo1. n/a2. SpongYe2. n/a3. n/a3. n/a4. n/a4. n/a5. n/a5. n/a   Week 4 Results:  Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Copilot Cookbook Gallery:Power Apps Cookbook Gallery:1. @nickpotts10  1. @ShrushtiShah  2. @Suniti_0020 2. @swaminawale 3. n/a3. @farukhis786 4. n/a4. @ManishSolanki  5. n/a5.  n/a