Linking two tables together to compare LatLong and find the closest person (GeoFencing)

I am wondering how to link two tables together (reference) so i can use LatLong in two scenarios:

1st Scenario:I am looking at Table 1 on a map (contains assets) and i want to know which is the closest driver (Table 2 ) to an asset based on LatLong

2nd Scenario: I am looking at Table 2 on a map (drivers) and i want to know which is the closest asset (Table 1)to a driver based on LatLong

I will need to, if possible, use VC

Do you have some sample data you should share here?

Yes, sorry.

Here is sample of the Table 2 (drivers):

Here is the sample of Table 1 (assets):

So sorry I ran out of time today… I was going to see if you had some actual data sheets you could share as opposed to screen shots.

Do you know about the mercator calculation?


https://www.movable-type.co.uk/scripts/latlong.html

I was going to suggest building these in AppSheet but I now realize that appsheet does not appear to have Sin() or Cos() or Radian(). So I think you will need to build these directly in your spreadsheet.

Maybe someone here has a better solution to present, also…

Thanks

AppSheet has DISTANCE(…), which should work to calculate the distance between two LatLongs, at least on a basic level. But as for an example of how to accomplish calculating and then finding the smallest between your two tables, I don’t have one at the moment. I will think about it though.

Thank you

To find the 5 closest entries in Table 2 from an entry in Table 1:

TOP(
  ORDERBY(
    FILTER("Table 2", TRUE),
    DISTANCE(
      LATLONG([_THISROW].[Lat], [_THISROW].[Long]),
      LATLONG([Lat], [Long])
    )
  ),
  5
)

Note that this is a very expensive operation and will affect sync times.

3 Likes

Thanks Steve. I will try this out.

1 Like

Steve - do i put this in Table 1 as a VC?

That’s one way to do it, yes.

ok. I am getting an error. Is the error because my name is [Last Latitude] and not [Lat]?

@Tiger

The query @Steve gave was for Table 2 usage. It seems you applied it to Table 1. So, Yes, you’ll need to adjust the column names to match those found in Table 1.

1 Like

Ok. Thanks i moved it to the correct table - Table 2 [GeoTab].

Now i get this error?

I believe LatLong types are expected to be numeric pairs (number1,number2). In your case you have the parts separated. So i think you just need to change the type of Lat and the type of Long to be Decimal.

1 Like

Wow. This works. This is a game changer for my company. Thank you guys VERY much. Last question with this. How can i make it show the Name instead of the id/key?

The values are showing the id/key. I have the label on the driver name. Can i make the formula show that instead of the id/key?

1 Like

Ok, so a couple of things. If you are going to store the individual Latitudes and Longitudes, then those columns should be in a decimal format. And for your LatLong column your formula should be just LATLONG([LAT], [LONG]).

1 Like

You are showing a list of Truck ID’s…correct? I don’t know of a way to have it automatically use the Label for display inn this instance. You want the ID’s to store in the record.

You have a couple of options:

  1. Create a Virtual Column that retrieves the Names for these ID’s and show the VC instead of the actual column.

  2. Change “Closest Truck” to show as an Inline Table - a row for each truck. The advantage is that you can show other Truck info along with the name.

1 Like

So the VC needs to go on which Table:
Table 1 (Assets) or Table 2 (Trucks)

Also, where do i put in this formula to show the new VC i create instead of the id/key?

TOP(
ORDERBY(
FILTER(“GeoTab”, TRUE),
DISTANCE(
LATLONG([_THISROW].[Last Latitude], [_THISROW].[Last Longitude]),
LATLONG([Lat], [Long])
)
),
5
)

@Tiger for AppSheet to grab the label instead of the key in that instance, I believe you need to mark it as a Ref column.