Help with DISTANCE Needed

Ryan_Wagner · 06-15-2021 01:32 PM

Hi everyone. I’ve been having a blast with AppSheet and discovering how much can be done with it.

Currently, I’m trying to create a slice of a table where the only pins that will show up on the map are those within a half-mile radius of another column if it is populated.

In other words, I’m trying to pull all the lat,long(s) that are within a half-mile of the items in another column if the row is “not blank.” I have lat,long for these items, and I can get the formula to return with no errors, but it is not working.

Here’s what I have now:

DISTANCE([OBJECTID],[Lat,Long])<0.805,CONTAINS([OS_ASSET_ID], “Gravid Trap”)

The OBJECTID column has all the items I’m trying to narrow down to less than a half-mile. The OS_ASSET_ID column has the pins that I’m using as the center of the radius.

Did I explain that adequately?

Can anyone lend me a hand?

Thanks!

Rafael_ANEIC-PY

Hello @Ryan_Wagner, it looks like you have two conditions for your slice, you need to wrap both conditions with an AND() expression in order for Appsheet to check for both conditions.

Also i’m not sure i’m following your column structure, your expression should be like this:

AND(DISTANCE([Latlong1],[Latlong2])<0.805,CONTAINS([text to search],"text to look for"))

Ryan_Wagner

That is very helpful, thanks! If I can’t get it to work tomorrow, I’ll reply here with a better description of my column situation. Thanks again. Good to know that what I’m after is possible!

Ryan_Wagner

@Rafael_ANEIC-PY

Finally got around to trying this again…

So…I set one table up that will populate with items that have lat, long…

My other table is the master database.

When the second table gets a new item added, I want to be able to bring up every item from the first table that is within a half-mile…

Would you mind taking another look at this? I would use this feature almost daily if I could get it to work.

Hopefully my goal makes sense. Thanks a lot for the help!

Rafael_ANEIC-PY

Hello @Ryan_Wagner , does this expression work for you?

FILTER(Master_database[OBJECTID],DISTANCE([Lat,Long],[_THISROW].[Lat,Long])*0.621371<0.5)

It should be used as a valid_if expression for the column you wish to populate.

Ryan_Wagner

I really appreciate you taking the time to help me with this…I would never have figured out that formula (or anything close to it) on my own.

When I pasted that expression in and changed the table name from Master_database to the table I need, it shows up as an error:

Any ideas on that issue?

Thanks again.

Rafael_ANEIC-PY

Hmm, maybe it’s the decimals, can you try playing a bit with them? i suspect it’s the decimal digits or maybe the decimal separator, or maybe try this one instead:

FILTER("CITY_OF_ELK_GROVE_INLET",(DISTANCE([Lat,Long],[_THISROW].[Lat.Long])*0.62)<0.50)

Ryan_Wagner

This seems like it’s getting closer?

Rafael_ANEIC-PY

Hold on, you’re using that expression as a slice condition? I thought we’re gonna use it as a valid_if() condition.

Ryan_Wagner

I was trying to do that…Probably do to my lack of understanding.

If I set this up as a view instead of a slice, that may help?

Rafael_ANEIC-PY

I see, well, my idea was that you’re gonna have a column in your main table that’s going to be a reference to the table with all those locations, and with that column’s valid_if() using this expression:

You’re gonna get a list of all the closests locations that satisfy the half a mile requirement, but then again i think i kinda lost track of the discussion since some time has passed haha, let me know if this answers your question.

Ryan_Wagner

I totally get it, it’s always complicated trying to dive into someone else’s system…This forum seems like a great place to get help, and I really appreciate you taking your time to help.

So, I now added this expression to a view (view based on my main table) instead of a slice. Sadly, it’s still showing an error…

This is all probably because I didn’t set things up in the best way…

One last attempt to explain my system…I have lots of items in my main table (OBJECTID.) Some of those items also have another column filled out (Trap Radius.) I am trying to get a map view where all the OBJECTIDs within a half-mile of the OBJECTIDs with a “Trap Radius” item will show up…

Seriously, thanks for the help. If you have any other thoughts, or if I need to set up my tables differently, please let me know!

-Ryan

Steve

Add a virtual column to the “master database” table with an App formula expression of:

FILTER(
  "other-table",
  AND(
    ISNOTBLANK([Lat,Long]),
    ((DISTANCE([Lat,Long], [_THISROW].[Lat,Long]) * 1.60934) < 0.5)
  )
)

Replacing other-table with the name of the table containing the rows ti filter.

The first argument of FILTER() should be a table name only, not a table-column reference. Hence this error:

Replace FILTER with SELECT to correct the expression, or remove the column refernce in the first argument.

Yep.

It seems “OBJECTID” has many different uses. You need a much less confusing naming scheme. You need to clearly indicate your table names, and clearly indicate which table you’re referring to when you reference a column name.

It’s not at all clear what you want done with Trap Radius. Does it have something to do with this from your first post?

This makes absolutely no sense:

This is easy:

But you’ve done a great job of adding confusion.

Ryan_Wagner

Thanks for the help. I wasn’t trying to be confusing. I’m new to the platform, and it appears that I may have jumped in without understanding the best way to start.

I’ll leave this thread alone, but I did plan on asking future questions in the community. I’ll try and make them as clear as possible. That said, for the total outsider, the possibilities with AppSheet and the familiarity needed to interact in this community can present a learning curve.

Thanks.