Lat/Long Lookup/Rollup Question

Hey Dillon, would love to help but could use a specific example to help clarify things

Let’s use Crossing 1 as an example. You mentioned the tolerance zone is 0.5 x average difference between US & DS Lat/Long, respectively. With reference to the figures below, could I know how we would calculate said tolerance zone(s)?

Thanks

• Joint 120

  • US Lat: 42.22525650
  • US Long: 177.31347820
  • DS Lat: 42.22525140
  • DS Long: 177.31347820

• Joint 130

  • US Lat: 42.22525140
  • US Long: 177.31347820
  • DS Lat: 42.22523890
  • DS Long: 177.31347850

• Crossing 1

  • Lat: 42.2252514
  • Long: 177.3134782

Adam_C,

Thanks for the interest and willingness to help. There are two columns on the “Toy Joint Log” tab labeled Latitude Delta and Longitude Delta which finds the difference between US & DS respectively. I intend to use the average of those numbers, currently 0.0001349 for latitude and 0.0000117 for longitude. Does this help?

Regards,
Dillon

Hi Dillon, thanks for the details!

Let me know if the following logic is correct:

0.5 x average difference lat = 0.5 x 0.0001349 = 0.00006745
0.5 x average difference long = 0.5 x 0.0000117 = 0.00000585

Lower lat tolerance range for Crossing 1 = 42.2252514 - 0.00006745 = 42.22518395
Upper lat tolerance range for Crossing 1 = 42.2252514 + 0.00006745 = 42.22531885
Lower long tolerance range for Crossing 1 = 177.31347820 - 0.00000585 = 177.31347235
Upper long tolerance range for Crossing 1 = 177.31347820 + 0.00000585 = 177.31348405

If a Joint’s US/DS lat value is larger than or equals to 42.22518395 AND lower than or equals to 42.22531885, then we want to know about it
If a Joint’s US/DS long value is larger than or equals to 177.31347235 AND lower than or equals to 177.31348405, then we want to know about it

Apologies for the questions; once I know exactly how the math works I’ll be able to put a script together for you

Adam_C,

Thanks for the requested clarification, and your willingness to work on a script for me (that’s awesome!). Yes, this is exactly what I’m looking to do. It should be noted that eventually there will be about 45,000 joints of pipe (records) separated into 25 different system areas. The systems will be kept in a 3rd table and linked to the joints. I’d like to do a rollup formula that looks for the average tolerance and divides that by 2 (I know how to do that one). I bring this up because the the formula starts to look more like this:

(Using rough excel terms in the following)
AND( IF( AND( {Toy Joint Log Joint # Avg Latitude}>=({Applicant LAT} + {Avg Lat Delta/2},{Toy Joint Log Joint # Avg Latitude}<=({Applicant LAT} - {Avg Lat Delta/2}), [list Joint],“review submission latitude”), IF( AND( {{Toy Joint Log Joint # Avg Longitude}>=({Applicant LONG} + {Avg Long Delta/2},{Toy Joint Log Joint # Avg Longitude}<=({Applicant LONG} - {Avg Long Delta/2}), [list Joint],“review submission longitude”)

Does this make sense? It’s pretty rough.

Hi Dillon, thanks for the formula. Mine was going to be:

AND( 
  {Toy Joint Log Joint # US Latitude}>=({Applicant LAT} + {Avg Lat Delta/2}),
  {Toy Joint Log Joint # DS Latitude}<=({Applicant LAT} - {Avg Lat Delta/2}),
  {Toy Joint Log Joint # US Longitude}>=({Applicant LONG} + {Avg Long Delta/2}),
  {Toy Joint Log Joint # DS Longitude}<=({Applicant LONG} - {Avg Long Delta/2})
)

So seeing that we’re using the Avg Lat / Long was helpful

Roger that. From what I can tell, the system areas should not affect what we’re trying to achieve here, would that be right?

With 45,000 records, we might not be able to run this in an automation script, as they have a max run time of 30 seconds. If so, we’d have a Script App instead and you’d run it on a button click

AND( 
  {Toy Joint Log Joint # US Latitude}>=({Applicant LAT} + {Avg Lat Delta/2}),
  {Toy Joint Log Joint # DS Latitude}<=({Applicant LAT} - {Avg Lat Delta/2}),
  {Toy Joint Log Joint # US Longitude}>=({Applicant LONG} + {Avg Long Delta/2}),
  {Toy Joint Log Joint # DS Longitude}<=({Applicant LONG} - {Avg Long Delta/2})
)

Adam_C,

This is so cool that you’re so willing to help with this, wish I was somewhere close so that I could buy you a beer as just a thank you for the consideration so far. With respect to the different systems, that will play a part because we have 50, 60 & 80 foot pipe lengths between those systems. That, however should be cured by the rollup, so I think the formula you’re headed towards will work (close counts). Also the button solution is elegant and will work great with the interface I need to use with this data.

In looking at your profile, I see you are a freelance programming consultant. I’m thinking that it might be wise for us to connect next week to carry this conversation further. I’ll message you directly on the matter. Thanks!