Return Nearest Record via LatLong

AppSheet AppSheet Q&A
1

Hello All

I have 2 tables.

  1. Sites
    1. Field Name: Location
    2. Type : LatLong
  2. Lookouts
    1. Field Name : LatLong
    2. Type: LatLong

In a virtual column in the Sites Table I want to populate the nearest Lookout for each Site

Additionally when a new Lookout is added to its table run bot a notification when said Lookout is with 5 km of site.

But right now I just want to find the nearest Lookout.

:slightly_smiling_face:

2

Siehe folgenden Thread: Hilfe-bei-Distanz-zwischen-HIER-und-Latlong

3

The Relationship is 1 to many

1 Site to many Lookouts. Acceptance criteria would be to populate the closest Lookout to a particular site (in the sites table). The relationship is dynamic. Meaning if a new lookout was added to the lookout table with a closer LatLong recalculate to update that Site with the new closer lookout. :slightly_smiling_face:

4

Given that’s the case, you should be able to add a column to the lookouts table that calculate each lookout’s distance to its site. Then, in the sites table it should be somewhat straightforward to identify the closest lookout in your virtual column. Something like the following might work:

MINROW("Lookouts", [Distance], [Site ID] = [_THISROW].[Site ID])
5

Thank you for the reply :slightly_smiling_face:

How would I go about:

@dbaum wrote:

Given that’s the case, you should be able to add a column to the lookouts table that calculate each lookout’s distance to its site.

Screenshot 2023-06-26 at 9.30.13 AM.png
Screenshot 2023-06-26 at 9.30.13 AM.png1752Ă—1212 360 KB

6

DISTANCE() - AppSheet Help For example:

DISTANCE([Lookout Location], [Site ID].[Site Location])

However, based on your diagram, it looks like you don’t indeed have a one-to-many relationship between sites and lookouts. Your illustration suggests it’s actually many-to-many (or, even, just outright all-to-all). If that’s the case, then you don’t have your tables referenced as I inferred and need to identify a different approach–probably create a dedicated table with a row for every site-lookout combination with those columns referencing their respective tables and with the distance for each combination calculated there.

7

Yes. I see that now. It will be tough with the lookout table being so dynamic (lookouts coming and going)

8

ICYMI: You could presumably use actions (and, if necessary, automations) to add and delete site-lookout combinations as your lookouts are added and deleted.

9

This worked as a solution

FILTER(
“Lookouts”,
AND(
ISNOTBLANK([LatLong]),
((DISTANCE([LatLong], [_THISROW].[LatLong]) * 1.60934 * 0.6213712) < 5.0)
)