cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
natrob
Helper I
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
v-bofeng-msft
Community Support
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:

31.gif

I think this link will help you a lot:

Bing Maps (Preview) 

Best Regards,

Bof

View solution in original post

8 REPLIES 8
WarrenBelz
Super User
Super User

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.

v-bofeng-msft
Community Support
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:

31.gif

I think this link will help you a lot:

Bing Maps (Preview) 

Best Regards,

Bof

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.

 

BingError.png

v-bofeng-msft
Community Support
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

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

 

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

Hi WarrenBelz

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

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.

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (6,676)