Google Sheets – Calculate Geographic Area

google sheets

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?

Best Answer

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 m².

Related Topic