3

I have three columns of cells with geographic coordinates. For example:

40.614362,-73.974187 | 40.618654,-73.989636 | 40.629675,-73.979092

I want to calculate an area between these coordinates to get similar results as I would get from http://www.daftlogic.com/projects-google-maps-area-calculator-tool.htm It seems like a regular "planar" formula for a triangle area as seen here https://math.stackexchange.com/a/516223/195545 does not produce accurate results.

Note that latitude and longitude coordinates are in the same cell. I figured out that I can use the following formula to extract latitude and longitude values:

 =REGEXEXTRACT(A1,"(.*?),")  # for latitude
 =REGEXEXTRACT(A1,",(.*?)$") # for longitude

How can I reproduce area calculations in Google Sheets?

  • 3
    Those coordinates look pretty close together, but note that the shoelace formula only applies for a plane and shouldn't be used if the points on Earth are far enough that the planet's curvature matters. – Shane Di Dona Nov 26 '14 at 6:25
  • 1
    @ShaneDiDona, you are right. I rephrased the question to address this concern. – Mxx Nov 26 '14 at 19:25
1

With a help from a friend, he was able to create the following JavaScript which was imported into Google Sheets' Script Editor and then referenced as =PlanarPolygonAreaMeters2(A1,B1,C1)/1000000

var radiansPerDegree = Math.PI / 180.0;
var degreesPerRadian = 180.0 / Math.PI;
var earthRadiusMeters = 6367460.0;
var metersPerDegree = 2.0 * Math.PI * earthRadiusMeters / 360.0;
var metersPerKm = 1000.0;
var meters2PerHectare = 10000.0;
var feetPerMeter = 3.2808399;
var feetPerMile = 5280.0;
var acresPerMile2 = 640;

function PlanarPolygonAreaMeters2(p1, p2, p3) {
    var points = [p1, p2, p3];
    Utilities.sleep(1500);
    var a = 0.0;
    for (var i = 0; i < points.length; ++i) {
        var j = (i + 1) % points.length;

        var thisPointRaw = points[i];
        var nextPointRaw = points[j];
        var thisPointLat = thisPointRaw.split(/,/)[0];
        var thisPointLng = thisPointRaw.split(/,/)[1];

        var nextPointLat = nextPointRaw.split(/,/)[0];
        var nextPointLng = nextPointRaw.split(/,/)[1];

        var xi = thisPointLng * metersPerDegree * Math.cos(thisPointLat * radiansPerDegree);
        var yi = thisPointLat * metersPerDegree;
        var xj = nextPointLng * metersPerDegree * Math.cos(nextPointLat * radiansPerDegree);
        var yj = nextPointLat * metersPerDegree;
        a += xi * yj - xj * yi;
    }
    return Math.abs(a / 2.0);

}

The raw output of this script is in .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.