Calculate new coordinate with distance

[D1XII]

In my mind this is simple - it is probably an excel nightmare:

My company has a geographical map that needs thousands of coordinates pinned so it can be accessed through a map app. The coordinates are close - so I want to pin one coordinate and calculate the next by adding a few feet in between.

For example: 23° 9'28.19"N, 81°55'34.06"W becomes 23° 9'28.19"N, 81°55'34.48"W after adding ~10ft to the West.

In my mind the xls would be simple:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Reference Latitude
[/TD]
[TD]Reference Longitude
[/TD]
[TD]Distance (in feet)
[/TD]
[TD]Direction from Reference to New Point
[/TD]
[TD]New Lat
[/TD]
[TD]New Long
[/TD]
[/TR]
[TR]
[TD]23° 9'28.19"N
[/TD]
[TD]81°55'34.06"W
[/TD]
[TD]10
[/TD]
[TD]West
[/TD]
[TD]23° 9'28.19"N
[/TD]
[TD]81°55'34.48"W
[/TD]
[/TR]
</tbody>[/TABLE]


I am wrecking my brain to figure something out - I definitely cannot pin 70K points on a map manually Advanced appreciation!

 

[Gerald Higgins]

Let's assume that 10 ft does actually = 0.42 seconds - I don't know if that's right or not.

What exactly do you need help with ?

Do you need help with writing a formula that takes a set of co-ordinates, and adds 10 ft (or some other distance) to them and comes up with some new co-ordinates ?

 

[Kerryx]

looks like you'd have to reverse this scenario?
https://sciencing.com/convert-latitude-longtitude-feet-2724.html

 

[D1XII]

Exactly. Assuming 10ft = .42 seconds I would need the formula to add .42 second to the reference coordinate and to each subsequent coordinate calculation.

 

[D1XII]

Let's assume that 10 ft does actually = 0.42 seconds - I don't know if that's right or not.

What exactly do you need help with ?

Do you need help with writing a formula that takes a set of co-ordinates, and adds 10 ft (or some other distance) to them and comes up with some new co-ordinates ?

Yes, Gerald. That is exactly it. Do you have any ideas? The more I attempt, the more confused I am getting.

 

[shg]

Are the points always directly N/S/E/W of the starting point, and can you assume the earth is a sphere? If so, that's straightforward.

 

[Gerald Higgins]

Also, can you tell us please how exactly your co-ordinates are stored in your Excel worksheet ?

 

[D1XII]

Are the points always directly N/S/E/W of the starting point, and can you assume the earth is a sphere? If so, that's straightforward.

Hi shg - yes the points will always be N,S,E or W and the earth is a normal sphere.

 

[D1XII]

Also, can you tell us please how exactly your co-ordinates are stored in your Excel worksheet ?

Yes, the coordinates are in LAT/LONG [DMS] format and the output of the new coordinates would need to follow so I can import them back into the software. I don't know if that answers the question or not?

 

[shg]

I think this is correct:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Lat​

[/td][td="bgcolor:#F3F3F3"]

Long​

[/td][td="bgcolor:#F3F3F3"]

Dist​

[/td][td="bgcolor:#F3F3F3"]

Dir​

[/td][td="bgcolor:#F3F3F3"]

New Lat​

[/td][td="bgcolor:#F3F3F3"]

New Long​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

23° 9'28.19"N​

[/td][td]

81°55'34.06"W​

[/td][td]

10​

[/td][td]

N​

[/td][td="bgcolor:#CCFFCC"]

23°9'28.29"N​

[/td][td="bgcolor:#CCFFFF"]

81°55'34.06"S​

[/td][td][/td][td="bgcolor:#CCFFCC"]E2: =Deg2DMS(DMS2Deg(A2) + CHOOSE(SEARCH(D2, "NESW"), CONVERT(C2, "ft","m") / 111120, 0, -CONVERT(C2, "ft","m") / 111120, 0), "NS")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

23° 9'28.19"N​

[/td][td]

81°55'34.06"W​

[/td][td]

10​

[/td][td]

E​

[/td][td="bgcolor:#CCFFCC"]

23°9'28.19"N​

[/td][td="bgcolor:#CCFFFF"]

81°55'33.95"S​

[/td][td][/td][td="bgcolor:#CCFFFF"]F2: =Deg2DMS(DMS2Deg(B2) + CHOOSE(SEARCH(D2, "NESW"), 0, CONVERT(C2, "ft","m") / 111120 / COS(RADIANS(DMS2Deg(A2))), 0, -CONVERT(C2, "ft","m") / 111120 / COS(RADIANS(DMS2Deg(A2)))), "NS")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

23° 9'28.19"N​

[/td][td]

81°55'34.06"W​

[/td][td]

10​

[/td][td]

S​

[/td][td="bgcolor:#CCFFCC"]

23°9'28.09"N​

[/td][td="bgcolor:#CCFFFF"]

81°55'34.06"S​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

23° 9'28.19"N​

[/td][td]

81°55'34.06"W​

[/td][td]

10​

[/td][td]

W​

[/td][td="bgcolor:#CCFFCC"]

23°9'28.19"N​

[/td][td="bgcolor:#CCFFFF"]

81°55'34.17"S​

[/td][td][/td][td][/td][/tr]
[/table]

A uses a pair of UDFs:

Function DMS2Deg(ByVal s As String) As Double
    ' Converts dd° mm' ss.ss{N|E|S|W}" to decimal degrees

    Dim iSgn        As Long

    s = Replace$(s, " ", "")
    Select Case UCase(Right$(s, 1))
        Case "N", "E"
            iSgn = 1
            s = Left$(s, Len(s) - 1)
        Case "S", "W"
            iSgn = -1
            s = Left$(s, Len(s) - 1)
    End Select

    s = Replace$(s, "°", "+")        ' replace the degree sign
    s = Replace$(s, "'", "/60+")     ' replace the minute sign
    s = Replace$(s, """", "/3600")   ' replace the second sign (double quote)
    s = Replace$(s, "''", "/3600")   ' replace the second sign (two single quotes)
    If Left$(s, 1) = "-" Then
        DMS2Deg = -iSgn * Evaluate(Mid$(s, 2))
    Else
        DMS2Deg = iSgn * Evaluate(s)
    End If
End Function

Function Deg2DMS(ByVal d As Double, sLL As String) As String
  Dim sDir As String
  Dim dDeg As Double
  Dim dMin As Double
  Dim dSec As Double
  
  ' sLL is "NS" for latitude and "EW" for latitude
  
  If d >= 0 Then
    sDir = Left(sLL, 1)
  Else
    sDir = Mid(sLL, 2, 1)
    d = -d
  End If

  dDeg = Int(d)
  d = 60# * (d - Int(d))
  dMin = Int(d)
  dSec = 60# * (d - Int(d))
  Deg2DMS = dDeg & "°" & dMin & "'" & Format(dSec, "0.00") & """" & sDir
End Function