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?
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.
Thanks Steve. I will try this out.
Steve - do i put this in Table 1 as a VC?
That’s one way to do it, yes.
@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.
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.
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?
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]).
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:
Create a Virtual Column that retrieves the Names for these ID’s and show the VC instead of the actual column.
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.
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.
