Mathematically challenged, not an Excel guru, need help
February 13, 2015 8:47 AM Subscribe
I am trying to figure out a way to use Excel to take an initial lat/long coordinate and then add a pre-determined figure to create offset coordinate points.
EG: IP lat/long
add X to create offset
add X to create offset
and so on.
Kind of time sensitive so I figured I would throw it out here in the event that someone has done this. My initial search on the net is bringing up a lot of info on calculating between points. I just want to take a point and add an offset, more likely 8 additional offsets. My initial offsets will be longitudinal.
Thanks for any assistance provided.
EG: IP lat/long
add X to create offset
add X to create offset
and so on.
Kind of time sensitive so I figured I would throw it out here in the event that someone has done this. My initial search on the net is bringing up a lot of info on calculating between points. I just want to take a point and add an offset, more likely 8 additional offsets. My initial offsets will be longitudinal.
Thanks for any assistance provided.
Response by poster: A constant shift in longitude. A base lat/long then add a predetermined distance. I am trying to create nine points equidistant across a 1 KM line. Initially an east-west line. I want to be able to put the first lat/long in and have excel calculate the other 8 points and generate the lat/long for them.
The results then uploaded/imported to Earthpoint and viewed on google earth.
Thank you.
posted by a3matrix at 8:57 AM on February 13, 2015
The results then uploaded/imported to Earthpoint and viewed on google earth.
Thank you.
posted by a3matrix at 8:57 AM on February 13, 2015
Best answer: If you're looking to shift a distance Lx to the East and Ly to the north along the Earth's surface, then the changes in latitude λ and longitude φ will be
∆λ = Ly * 360/(6371 km * 2π)
∆φ = Lx * 360/(sin(λ) * 6371 km * 2π)
These will give answers in degrees; if for some reason you want answers in radians, then leave out the 360's and 2π's. I'm not sure if Excel expects the argument to its sine function to be in radians or degrees, but if it expects input in degrees, then you'll have to do a similar compensation there.
(Note that by "to the North" and "to the East" above, I mean "along a longitude line" and "along a latitude line". Latitude lines aren't really straight lines on the Earth's surface, since they're not great circles; but I assume that your distances aren't great enough to need to worry about that.)
posted by Johnny Assay at 9:07 AM on February 13, 2015
∆λ = Ly * 360/(6371 km * 2π)
∆φ = Lx * 360/(sin(λ) * 6371 km * 2π)
These will give answers in degrees; if for some reason you want answers in radians, then leave out the 360's and 2π's. I'm not sure if Excel expects the argument to its sine function to be in radians or degrees, but if it expects input in degrees, then you'll have to do a similar compensation there.
(Note that by "to the North" and "to the East" above, I mean "along a longitude line" and "along a latitude line". Latitude lines aren't really straight lines on the Earth's surface, since they're not great circles; but I assume that your distances aren't great enough to need to worry about that.)
posted by Johnny Assay at 9:07 AM on February 13, 2015
Response by poster: Thanks for your help Johnny. The last piece of the puzzle I have is how to apply it to an excel sheet. As stated in my post title, I am not an excel guru.
I appreciate your time and assistance.
a3
posted by a3matrix at 9:38 AM on February 13, 2015
I appreciate your time and assistance.
a3
posted by a3matrix at 9:38 AM on February 13, 2015
This thread is closed to new comments.
posted by Johnny Assay at 8:52 AM on February 13, 2015