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

[Tiger1]

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

[TyAlevizos]

Do you have some sample data you should share here?

[Tiger1]

Yes, sorry.

Here is sample of the Table 2 (drivers):

Here is the sample of Table 1 (assets):

[TyAlevizos]

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?

gis.stackexchange.com

Calculating distance scale factor by latitude for Mercator

coordinate-system, distance, mercator

asked by user1702401 on 07:57PM - 16 Apr 14 UTC

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…

[Tiger1]

Thanks

[Bahbus]

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.

[Tiger1]

Thank you

[Steve]

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.

[Tiger1]

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

[Steve]

That’s one way to do it, yes.

[Tiger1]

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

[WillowMobileSys]

@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.

[Tiger1]

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

Now i get this error?

[WillowMobileSys]

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.

[Tiger1]

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?

[WillowMobileSys]

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.

[Tiger1]

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
)

[Bahbus · Accepted solution]

@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.

[WillowMobileSys]

@Bahbus

@Tiger is grabbing the TOP 5 Closet Trucks. How does one get that to work with a Ref Column?

[Bahbus]

Because it those are IDs for the table. It will work the same as any other “Related” generated column as a Ref. And what I meant by Ref, was List/Ref.

[WillowMobileSys]

@ Tiger It think @Bahbus has your answer. I have not played with List VC’s much. It seems you don’t need a second VC at all. Just go into the VC list you already have and change the Base Type property to “Ref”.

[WillowMobileSys]

Your VC for the Names would be on the same table as the list of Truck ID’s. You need the result from [Closest Trucks] to get their names. So keep it as is just don’t show it. Disregard what I said about storing these ID’s.

The expression in this new VC would be something like:

SELECT(Trucks[Name], IN([Truck ID], [Closest Trucks]))

This will get you a list of the Names for display.

[Bahbus]

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]).

[Tiger1]

Thanks Steve. I will try this out.

[Tiger1]

ok. But do i do this in Table 1 or 2? Sorry i wanted to get clarification.

Also, do I use?
SELECT(Trucks[Name], IN([Truck ID], [Closest Trucks]))

[WillowMobileSys]

No, forget everything I said about a second VC. It seems you don’t need it.

Which table is the “Closest Truck” VC defined in?

[Tiger1]

Well. To be honest i never put a VC together. I still have the expression Steve gave me. Works - just still don’t show the truck driver names, instead of the id/key.

[WillowMobileSys]

Your ask was to change the ID’s in this pic below to show the Names. Is this not real data?

---

Below, Is this the column that shows that List of Truck ID’s?

[Tiger1]

it is real - but it is the id/key. I want the label [Driver] to show:

[WillowMobileSys]

Yes. Go into that column definition for Closest Asset. Change it from Text to EnumList. You will then be presented below that a property named “Base Type”. Set that to Ref. I think that will get you what you want.

[Tiger1]

This worked PERFECTLY. I still am not sure how, but it does. I went ahead and did this both ways. Meaning - Table 1 does the same thing. So my users can see:

Closest truck - if looking at the asset
Closest asset - if looking at the truck

Thanks guys!

[WillowMobileSys]

Tiger:

I still am not sure how

When you specify a column as “Ref” AND the value set is the key value, AppSheet will automatically use the Label value for display.

If this is a “normal” column - the key is stored in the data BUT the label is used for display.

As you and I both just found out, if this is an EnumList with Base Type set as “Ref”, then the entire list will use the label for display.