Planimetric

[Cláudio Menoncin C. Pereira.]

I'd like to use Excel to calculate an area of land ,but i can't do it
because I don't know how to do calculations using degrees ,I've tried to
convert degrees to radians ,do the calculations and go back to degrees ,but
it not worked ,because it go beyond of 60' and it do a big error .If
someone knows anything about this ,please ,contact to me at
claume...@inbrapenet.com.br .Thanks.

[GDT]

emailed to: Cláudio Menoncin C. Pereira:

Hello. I read your post on msnews.microsoft.com (Excel programming
newsgroup). I understand that you are attempting to determine the distance
between two points using spherical coordinates. Ultimately, you want to
calculate the area bounded by the lines connecting these points--thus, the
length of these line segments must be determined.

Further below, I have provided expressions that approximate the distance
between two points on a sphere. These expressions are taken from spherical
trigonometry. The earth is not a perfect sphere, so there is some error.
The expressions determine the arc length between two-points lying on a
"Great Circle" of a sphere. The earth's equator is a "Great Circle." A
great circle is a circle of largest diameter obtained by slicing a sphere
with a plane.

Using spherical coordinates, a point will be denoted by an ordered pair of
the form (latitude in dms, longitude in dms), where dms is degrees, minutes
and seconds; one-minute of great circle arc is one-nautical
mile--approximately 6,071-feet. The error in the following expressions may
be examined by analyzing points on the equator: i.e., all points with
0-latitudes. These expressions calculate one-minute of great circle arc to
a value of 6,040-feet--an error of about 0.5 of one-percent. I'll use
kilometers (km) as the unit of measure. The earth's approximate radius is
6,370.9 km when considered a sphere.

In Excel, setup a model for your data points. Here's an example.

A BCD E F

1 Degrees Radians
2 DMS Decimal Decimal
3 Point A Lat =B3+C3/60+D3/3600 =E3*PI()/180
4 Point A Long =B4+C4/60+D4/3600 =E4*PI()/180
5
6 Point B Lat =B6+C6/60+D6/3600 =E6*PI()/180
7 Point B Long =B7+C7/60+D7/3600 =E7*PI()/180
8
9 Radius 6370.9
10 Distance

Now, name cell F3, LatA; name cell F4, LongA; name cell F6, LatB; name cell
F7, LongB; and name cell E9, Radius.

Enter data for two points; for point A, enter degrees in cell B3; enter
minutes in cell C3; and enter seconds in cell D3. You should enter integer
values for degrees and minutes: 0 <= degrees <= 59 and 0 <= minutes <= 59.
Seconds may be entered with decimal expansions. If your data is in decimal
(not DMS), then enter the decimal values in column E instead of the
formulas. Additionally, enter all northern-latitudes as positive, and
enter all southern-latitudes as negative. Enter all eastern-longitudes as
positive, and enter all western-longitudes as negative. These sign
assignments are the same as the signs in Cartesian quadrants. You may
reverse them if it will serve you better. Signs are important only if two
points are in different hemispheres. If your data is in DMS, then enter
each number with the proper sign:e.g., -D, -M, -S if the data is southern
or western.

In cell E10, enter the following formula:

=Radius*Acos(cos(LatA)*cos(LatB)*cos(LongA - LongB) + sin(LatA)*sin(LatB))

The result of this expression is the distance in the same unit of measure
as the radius--in this example, the unit of measure is km. To get results
in statue miles, enter 3936 in cell E9--this value, 3936, is the earth's
approximate radius in statue miles when considered a sphere. To express
results in feet, use 3936 * 5280 (there are 5,280-feet per statue mile).

The expression, cos(LatA)*cos(LatB)*cos(LongA - LongB) +
sin(LatA)*sin(LatB), in the distance formula is the cos of the angle
between the radii of each point. It is a dot product. If points A and B
are on opposite sides of the equator, then sin(LatA)*sin(LatB) will be
negative. If LongA - LongB is greater than 90, then
cos(LatA)*cos(LatB)*cos(LongA - LongB) will be negative. Note that the
Acos function returns an angle in radians.

An alternative method is to convert from Spherical Coordinates to Cartesian
Coordinates. For example,

Point A
Xa = Radius * cos(LongA) * cos(LatA)
Ya= Radius * sin(LongA) * cos(LatA)
Za= Radius * sin(LatA)

PointB
Xb= Radius * cos(LongB) * cos(LatB)
Yb= Radius * sin(LongB) * cos(LatB)
Zb= Radius * sin(LatB)

Distance = Sqrt( (Xa-Xb)^2 + (Ya - Yb)^2 + (Za - Zb)^2 ); the result is in
the same unit of measure as Radius.

Finally, Arccos(X) = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1) may be used to
derive the ARCCOS function from the ATN function. This will be necessary
if you need to use VBA; in this expression for the ARCCOS, X is the angle
between the radii of the two-points. Also, you may use 2 * Atn(1) as given
or PI() / 2; i.e., 2 * Atn(1) = PI() / 2 ( when both sides of the equation
use the same number of decimal places).

You may email questions to expres...@msn.com.

Regards,
Glenn Tarpley
expres...@msn.com

Cláudio Menoncin C. Pereira. <claume...@inbrapenet.com.br> wrote in
article <01bc245d$669aa2e0$2307...@Claudio.inbrapenet.com.br>...