Converting Lat Lng to XY Coordinates: A Guide

As someone who works with geographic data, I often need to manipulate latitudes and longitudes. Recently, a researcher requested that I convert a list of latitudes and longitudes to XY coordinates to obscure the actual location of the points while still allowing for analysis of the distance relationship between them. Here’s how I did it, complete with formulas!

Step 1: Drop the Lat Lngs into Excel
First, I needed to get the latitudes and longitudes into a spreadsheet. I simply copied and pasted them into Excel.

Step 2: Calculate the X and Y Coordinates
I used the following formulas to calculate the X and Y coordinates, using a common latitude and longitude as a reference point:

X:

=(lon1 - lonconstant) * 40000 * COS((lat1 + latconstant) * PI() / 360) / 360

Y:

=(lat1 - latconstant) * 40000 / 360

These formulas use the distance from the reference point (in degrees) to calculate the distance in kilometers or miles, depending on the constant used.

Step 3: Calculate the Distance Between Points
Next, I needed to calculate the distance between the points. I used the following formula:

=SQRT((x2-x1)^2+(y2-y1)^2)

This formula calculates the Euclidean distance between two points, which is the straight-line distance between them. To check that the values were correct, I also calculated the distance using only the latitudes and longitudes with the following formula:

=6371*ACOS(COS(RADIANS(90-Y1))*COS(RADIANS(90-Y2))+SIN(RADIANS(90-Y1))*SIN(RADIANS(90-Y2))*COS(RADIANS(X1-X2)))

This formula calculates the great-circle distance between two points, taking into account the curvature of the Earth.

Step 4: Compare the Two Distances
To check that the values were correct, I compared the two distances to see how close they were. Then I measured the distance on Google Maps. Bingo! Keep in mind that they will never be perfect due to the way things are projected, but they should be relatively close.

And that’s it! Converting latitudes and longitudes to XY coordinates can be a useful technique when you need to obscure the actual location of the points while still allowing for analysis of the distance relationship between them. I hope this guide helps you in your own work with geographic data!

For anyone interested, here is a js version of the code below: