Hello,
I am wondering if it is possible to calculate the area of a geographical polygon defined by a series of latitudes and longitudes in MS Excel?
(I know this could be done in a GIS)
For example a field area defined by these coordinates:
39.001050,35.005661
38.999689,35.005031
38.999278,35.005808
38.998528,35.005555
38.998303,35.006244
39.000358,35.006931
Thanks in advance.
Sincerely,
Muharrem Keskin
If you close your polygon by adding the first points coordinates to the end of your X and Y column (you should have your X and Y values in separate columns) you can easily calculate the area. Otherwise the formulation won't be so easy.
In your example you have 6 points and you'll add the first one to the end. Including the title X and Y raw you'll have your data in raws 2 to 8. Then your formulation will be:
=ABS(SUMPRODUCT(A2:A7;B3:B8) - SUMPRODUCT(B2:B7;A3:A8))/2
Dear Ayda,
Thanks for your answer. You are right, to complete the polygon we need to add the first coordinate at the end;
39.001050,35.005661
38.999689,35.005031
38.999278,35.005808
38.998528,35.005555
38.998303,35.006244
39.000358,35.006931
39.001050,35.005661
I am using MS Excel 2010. I was not able to get the result.
Could you please explain how this calculation works?
Also, what is the unit of the result 0.0000028.
The area is about 33.5 decare (3.35 hectare) as calculated from the sizes found in Google Earth.
Thank you again.
Muharrem Keskin
First, the equation comes from the Gauss's Area Calculation Formula that defines an irregular polygon area with (x1,y1), (x2,y2),...(xN,YN) coordinates as:
(1/2)[{(x1*y2)+(x2*y3)+(x3*y4)...(xN*y1)} - {(y1*x2)+(y2*x3)+(y3*x4)...(yN*x1)}]
Most probably, using (.) instead of (,) while writing the coordinates may be the reason why Excel did not compute the formulation.
Here the important thing is the given formulation is based on cartesian coordinates (sorry that I forget to imply in my previous answer). Hence before using it you need to convert your geodetic coordinates to Cartesian coordinates using an appropriate projection. Based on your cartesian coordinate length unit your results unit will vary.
The method above by Ayda works well for cartesian coordinates and may not work well for Latitude/Longitude spherical coordinates. This is likely to give you an error in your area as you are not considering the projection to the plane.
Also consider, your longitude are meridian lines that pass through the North and South poles. The lines of latitude are circles orthogonal to the meridians, parallel to the plane of the equator and intersect the surface of the earth at a given angle to the plane of the equator.
Given this spherical geometry, understand that the surface of the earth subtended by a 1deg. latitude and 1deg. longitude "square" at the equator will NOT have the same area closer to the north or south pole. Simple example is a peel cut out of an orange from its top to its bottom, then divided into as many equidistant portions along the top to bottom direction as the widest part (left-right) of the peel. Note the sizes of the portions as you progress to the top or bottom.
What you need to do is project or convert LAT/LONG to UTM or whatever local plane coordinate system is used in the area, then apply Ayda's method.
Julian,
Thank you for your informative answer.
Best Regards,
Muharrem Keskin
The Gauss's formula as proposed by Dr. Ayda works very well for flat surfaces. It gives relatively good results for Latitude/Longitude spherical coordinates provided that the considered area has no more then 1o x 1o and could be approximated by a flat surface. For more extended surfaces, it is necessary to calculate the solid angle (in steradians0 subtended by the considered zone and then, by dividing it to 4 π and multiplying with the Earth's surface you will obtain the real values. Of course, this procedure does take into account the local relief of the considered area.
- Badges
- Science topic
- Similar topics
- Geoscience
- Geoinformatics
- Geographic Information System