Lat/Long Lookup/Rollup Question

[Dillon_O]

Greetings from a first time poster!

First off, thank you to anyone who might be able to help. I’d provide examples, but I think the question itself is pretty straightforward. Thank you to anyone who can provide direction on this.

In one table I have a few thousand records that represent segments of a pipeline. I have the latitude and longitude for both ends of the pipe (upstream “US” and downstream “DS”), as well as the average of both (6 columns). In another table I have a form setup that would represent crossings of said pipe, and it includes a latitude and longitude column as well. I want to setup a rollup or look-up column that provides the record (or records) from the first table that fall within a tolerance zone from the latitude/longitude entered from the form (0.5 x average difference between US & DS Lat/Long, respectively).

This is what the source table (1st table) looks like

This is what the Form table would look like, including what I’d like to see the outcome look like (without the manual entry).

Does this make sense to anyone out there?

Gratefully,
Dillon

[TheTimeSavingCo]

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

[Dillon_O]

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

[TheTimeSavingCo]

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

[Dillon_O]

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.

[TheTimeSavingCo]

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

Dillon_O:

It should be noted that eventually there will be about 45,000 joints of pipe (records) separated into 25 different system areas

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

[Dillon_O]

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!