With a bit of maths (to follow) you can get the following:
Longitude
=ATAN(F2/E2)
where E2 and F2 are X and Y
Latitude
=ACOS(SQRT(E2^2+F2^2)/6371)
So below I have started with the latitude and longitude of a famous northern city, converted them to X and Y coordinates, then converted them back into latitude and longitude. Because of the square root, there is actually a mirror image in the southern hemisphere.
The original equations can be written as:
X=R cos(lat) cos(long) ...(1)
Y=R cos(lat) sin(long) ...(2)
where R is the radius of the Earth in miles.
Dividing (2) by (1) gives
Y/X = tan (long)
or
long = atan(Y/X) ....(3)
Substituting (3) into (1) gives
X = R cos(lat) cos(arctan(Y/X))
but
cos(arctan(Y/X)) => X/sqrt(X^2 + Y^2)
(by considering a right-angled triangle with shorter sides X and Y)
so
X=R cos(lat) X / sqrt(X^2+Y^2)
cos(lat) = sqrt(X^2+Y^2) / X
lat = acos(sqrt(x^2+Y^2)/X) ....(4)
Equations (3) and (4) are used to determine the latitude and longitude from X and Y.
Answer from Tom Sharpe on Stack OverflowJust trying to follow @FelixIP's insightful comment;
Along with the original x and y, add two columns for lat and lon. (as A=x, B=y, C=lat, D=lon below.)
And enter formula into C and D cells as follows, which will give the output (value in red) also in the above image.
A2: x
B2: y
C2: =LEFT(B2,2) + (B2-(LEFT(B2,2)*100))/60
D2: =LEFT(A2,3) + (A2-(LEFT(A2,3)*100))/60
This is because of the different convention in a graph (paper based map) and grid (globe based map). In a graph, the x-axis increased to right and y-axis along an upward direction. value of X is written first followed by Y i.e. first right then up. But in the grid system, lat increases in upward direction and lng in right direction. This is similar to having YX graph, i.e first you have to go up then right. If this is not understood the points do not appear where they should appear and they are also rotated.
The data you have is in lnglat format instead of latlng, i.e. x in lng and y is lat
Converter for latitude/longitude feature in excel
Convert string of numbers to Lat Long in Excel
How to upload Coordinates from Excel to QGIS
Excel formula for converting from decimal to degree, minute, second.
Hi all!
I received a spreadsheet of attributes from which I need to create points. This is something I do a lot, but in the table I received from an outside source the Lat Long are just listed in a string of numbers and I don't know how to convert them to something the software will recognize. Or maybe that's not my problem at all and I need to find a different projection - but I don't think so.
The first time I tried this, it did seem that I used the wrong projection. The points did plot. I zoomed to layer and they were off the edge of the globe somewhere, but I could see them. So I tried again w/different projections, some standard ones.
Now, I Created Points from Table and the layer shows up in my catalogue/ Drawing Order, but nothing displays in my map. When I zoom to layer, the detail area doesn't change at all. I stay in the same place, but nothing displays. It doesn't matter where I am in the map. I've maneuvered around the geography a bit, and every time I stop somewhere and Zoom to Layer of these points, nothing happens. I stay wherever I was, as if that's where the layer should display, but nothing displays. Does that make sense?
So I think that part of my problem is that my Lat Long entries are not in a useable format. The top entry, for example, needs to be something like 39 79'10.44 and -105 15'86.11. Doesn't it? I thought maybe the software would know how to convert those, but it doesn't seem like it. And I'm not sure how to create a formula that will re-format those values in a new column in Excel so I can import it into Pro, or how to convert the values in ArcPro.
Does anyone know the answer? Thanks, all!!