Solved

# Distances based on GPS coordinates

Posted on 2011-04-24
1,019 Views
I have a four sided field with a center point (that is not really in the center) but is called center.

The four corners and "center" point are all known gps coordinates (known in UTM, Long Lat).  I need an excel calculation that will allow me to automatically calculate the distance from the "center" point to the edge of the field from all 365 degrees.  Assume that the four "corners" of the field can be connected to form a square if this makes this easier.  I also need this formula to be generic enough so that it can accommodate any 4 corners and "center point" and give me the distance automatically.

For instance, if I have a column 1a that says 231 (degrees), id like column 1b to say the distance to the edge of the field given 231 degrees from the "center" point
0
Question by:SullivanEnvironmental

LVL 43

Expert Comment

Can you give me a set of coorditantes?
0

Author Comment

Center      46      4      37.402            119      27      30.12
SW Corner      46      4      36.412            119      27      32.459
NW Corner      46      4      39.182            119      27      31.94
NE Corner      46      4      38.163            119      27      27.241
SE Corner      46      4      35.613            119      27      28.933
0

Author Comment

I have code that is being used to do this in Fortran.  I am not a programmer - but want to apply this basic idea to excel but with using the coordinates themselves - not converting them to an X Y grid.
C     Last change:  MTH  26 Oct 2010    4:27 pm
CHARACTER*20 DUM
INTEGER A
INTEGER PERIOD(33), Wind1, Wind2, Wind3, Wind4, Wind5
REAL SUMWS1, SUMWD1, SUMWS2, SUMWD2, SUMWS3, SUMWD3, SUMWS4
REAL SUMWD4, SUMWS5, SUMWD5, Angle, Fetch1, Fetch2, Fetch3
REAL Fetch4, Fetch5, SumFetch1, SumFetch2, SumFetch3, SumFetch4
REAL SumFetch5, X, Y
REAL WS1, WS2, WS3, WS4, WS5, WD1, WD2, WD3, WD4, WD5
REAL D1, D2, D3, D4, D5, D11, D12, D13, D14, D15

OPEN(1,FILE='ws1.prn',STATUS='OLD')
OPEN(2,FILE='wd1.prn',STATUS='OLD')
OPEN(4,FILE='met1.out',STATUS='UNKNOWN')

DATA PERIOD / 4, 6, 6, 6, 6, 6, 6, 6, 12, 12, 12, 12, 12, 12, 12,
x             12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12,
x             12, 12, 12, 14, 12/

Angle=45
X=68
Y=68
Pi=3.14159
MaxFetch=SQRT(X*X+Y*Y)/2

***   Set cumulative variables to zero for start of minute-hourly dataset conversion routine ***

DO A=1, 33

Wind1=0
SUMWD1=0
SUMWS1=0
Wind2=0
SUMWD2=0
SUMWS2=0
Wind3=0
SUMWD3=0
SUMWS3=0
Wind4=0
SUMWD4=0
SUMWS4=0
Wind5=0
SUMWD5=0
SUMWS5=0
SumFetch1=0
SumFetch2=0
SumFetch3=0
SumFetch4=0
SumFetch5=0

DO B=1,PERIOD(A)*60

READ(1,'(6F8.2)',END=20) WS1, WS2, WS3, WS4, WS5
READ(2,'(6F8.2)') WD1, WD2, WD3, WD4, WD5

IF (WS1.NE.0) THEN

Wind1=Wind1+1

IF (Wind1.EQ.1) THEN

D11=WD1
SUMWD1=D11

ELSE

TEST=WD1-D11

IF (TEST.LT.-180) THEN

D1=D11+TEST+360

ELSE IF (TEST.LT.180.AND.TEST.GT.-180) THEN

D1=D11+TEST

ELSE IF (TEST.GT.180) THEN

D1=D11+TEST-360

END IF

SUMWD1=SUMWD1+D1
D11=D1

END IF

END IF

SUMWS1=SUMWS1+WS1

IF (WD1-45.LT.0) THEN
Fetch1=WD1-45+360
ELSE
Fetch1=WD1-45
END IF

IF (Fetch1.GT.90) THEN
IF (Fetch1.GE.180) THEN
IF (Fetch1.GE.270) THEN
Fetch1=360-Fetch1
ELSE
Fetch1=Fetch1-180
END IF
ELSE
Fetch1=180-Fetch1
END IF
ELSE
Fetch1=Fetch1
END IF

COSA=ABS(0.5*Y/(COS(Fetch1*Pi/180)))
SINA=ABS(0.5*X/(SIN(Fetch1*Pi/180)))

IF (COSA.GT.MaxFetch) THEN
Fetch1=SINA
ELSE
Fetch1=COSA
END IF

IF (WS2.NE.0) THEN

Wind2=Wind2+1

IF (Wind2.EQ.1) THEN

D12=WD2
SUMWD2=D12

ELSE

TEST=WD2-D12

IF (TEST.LT.-180) THEN

D2=D12+TEST+360

ELSE IF (TEST.LT.180.AND.TEST.GT.-180) THEN

D2=D12+TEST

ELSE IF (TEST.GT.180) THEN

D2=D12+TEST-360

END IF

SUMWD2=SUMWD2+D2
D12=D2

END IF

END IF

SUMWS2=SUMWS2+WS2

IF (WD2-45.LT.0) THEN
Fetch2=WD2-45+360
ELSE
Fetch2=WD2-45
END IF

IF (Fetch2.GT.90) THEN
IF (Fetch2.GE.180) THEN
IF (Fetch2.GE.270) THEN
Fetch2=360-Fetch2
ELSE
Fetch2=Fetch2-180
END IF
ELSE
Fetch2=180-Fetch2
END IF
ELSE
Fetch2=Fetch2
END IF

COSA=ABS(0.5*Y/(COS(Fetch2*Pi/180)))
SINA=ABS(0.5*X/(SIN(Fetch2*Pi/180)))
IF (COSA.GT.MaxFetch) THEN
Fetch2=SINA
ELSE
Fetch2=COSA
END IF

IF (WS3.NE.0) THEN

Wind3=Wind3+1

IF (Wind3.EQ.1) THEN

D13=WD3
SUMWD3=D13

ELSE

TEST=WD3-D13

IF (TEST.LT.-180) THEN

D3=D13+TEST+360

ELSE IF (TEST.LT.180.AND.TEST.GT.-180) THEN

D3=D13+TEST

ELSE IF (TEST.GT.180) THEN

D3=D13+TEST-360

END IF

SUMWD3=SUMWD3+D3
D13=D3

END IF

END IF

SUMWS3=SUMWS3+WS3

IF (WD3-45.LT.0) THEN
Fetch3=WD3-45+360
ELSE
Fetch3=WD3-45
END IF

IF (Fetch3.GT.90) THEN
IF (Fetch3.GE.180) THEN
IF (Fetch3.GE.270) THEN
Fetch3=360-Fetch3
ELSE
Fetch3=Fetch3-180
END IF
ELSE
Fetch3=180-Fetch3
END IF
ELSE
Fetch3=Fetch3
END IF

COSA=ABS(0.5*Y/(COS(Fetch3*Pi/180)))
SINA=ABS(0.5*X/(SIN(Fetch3*Pi/180)))
IF (COSA.GT.MaxFetch) THEN
Fetch3=SINA
ELSE
Fetch3=COSA
END IF

IF (WS4.NE.0) THEN

Wind4=Wind4+1

IF (Wind4.EQ.1) THEN

D14=WD4
SUMWD4=D14

ELSE

TEST=WD4-D14

IF (TEST.LT.-180) THEN

D4=D14+TEST+360

ELSE IF (TEST.LT.180.AND.TEST.GT.-180) THEN

D4=D14+TEST

ELSE IF (TEST.GT.180) THEN

D4=D14+TEST-360

END IF

SUMWD4=SUMWD4+D4
D14=D4

END IF

END IF

SUMWS4=SUMWS4+WS4

IF (WD4-45.LT.0) THEN
Fetch4=WD4-45+360
ELSE
Fetch4=WD4-45
END IF

IF (Fetch4.GT.90) THEN
IF (Fetch4.GE.180) THEN
IF (Fetch4.GE.270) THEN
Fetch4=360-Fetch4
ELSE
Fetch4=Fetch4-180
END IF
ELSE
Fetch4=180-Fetch4
END IF
ELSE
Fetch4=Fetch4
END IF

COSA=ABS(0.5*Y/(COS(Fetch4*Pi/180)))
SINA=ABS(0.5*X/(SIN(Fetch4*Pi/180)))
IF (COSA.GT.MaxFetch) THEN
Fetch4=SINA
ELSE
Fetch4=COSA
END IF

IF (WS5.NE.0) THEN

Wind5=Wind5+1

IF (Wind5.EQ.1) THEN

D15=WD5
SUMWD5=D15

ELSE

TEST=WD5-D15

IF (TEST.LT.-180) THEN

D5=D15+TEST+360

ELSE IF (TEST.LT.180.AND.TEST.GT.-180) THEN

D5=D15+TEST

ELSE IF (TEST.GT.180) THEN

D5=D15+TEST-360

END IF

SUMWD5=SUMWD5+D5
D15=D5

END IF

END IF

SUMWS5=SUMWS5+WS5

IF (WD5-45.LT.0) THEN
Fetch5=WD5-45+360
ELSE
Fetch5=WD5-45
END IF

IF (Fetch5.GT.90) THEN
IF (Fetch5.GE.180) THEN
IF (Fetch5.GE.270) THEN
Fetch5=360-Fetch5
ELSE
Fetch5=Fetch5-180
END IF
ELSE
Fetch5=180-Fetch5
END IF
ELSE
Fetch5=Fetch5
END IF

COSA=ABS(0.5*Y/(COS(Fetch5*Pi/180)))
SINA=ABS(0.5*X/(SIN(Fetch5*Pi/180)))
IF (COSA.GT.MaxFetch) THEN
Fetch5=SINA
ELSE
Fetch5=COSA
END IF

SUMFETCH1=SUMFETCH1+Fetch1
SUMFETCH2=SUMFETCH2+Fetch2
SUMFETCH3=SUMFETCH3+Fetch3
SUMFETCH4=SUMFETCH4+Fetch4
SUMFETCH5=SUMFETCH5+Fetch5

END DO

SUMFETCH1=SUMFETCH1/(PERIOD(A)*60)
SUMFETCH2=SUMFETCH2/(PERIOD(A)*60)
SUMFETCH3=SUMFETCH3/(PERIOD(A)*60)
SUMFETCH4=SUMFETCH4/(PERIOD(A)*60)
SUMFETCH5=SUMFETCH5/(PERIOD(A)*60)
SUMWS1=SUMWS1/(60*Period(A))
SUMWD1=SUMWD1/Wind1
SUMWS2=SUMWS2/(60*Period(A))
SUMWD2=SUMWD2/Wind2
SUMWS3=SUMWS3/(60*Period(A))
SUMWD3=SUMWD3/Wind3
SUMWS4=SUMWS4/(60*Period(A))
SUMWD4=SUMWD4/Wind4
SUMWS5=SUMWS5/(60*Period(A))
SUMWD5=SUMWD5/Wind5

IF (SUMWD1.LT.0) THEN

SUMWD1=360+SUMWD1

ELSE IF (SUMWD1.GT.360) THEN

SUMWD1=SUMWD1-360

END IF

IF (SUMWD2.LT.0) THEN

SUMWD2=360+SUMWD2

ELSE IF (SUMWD2.GT.360) THEN

SUMWD2=SUMWD2-360

END IF

IF (SUMWD3.LT.0) THEN

SUMWD3=360+SUMWD3

ELSE IF (SUMWD3.GT.360) THEN

SUMWD3=SUMWD3-360

END IF

IF (SUMWD4.LT.0) THEN

SUMWD4=360+SUMWD4

ELSE IF (SUMWD4.GT.360) THEN

SUMWD4=SUMWD4-360

END IF

IF (SUMWD5.LT.0) THEN

SUMWD5=360+SUMWD5

ELSE IF (SUMWD5.GT.360) THEN

SUMWD5=SUMWD5-360

END IF

WRITE(4,'(I5,15F8.2)') A,
x            SUMWS1, SUMWS2, SUMWS3, SUMWS4, SUMWS5,
x            SUMWD1, SUMWD2, SUMWD3, SUMWD4, SUMWD5,
x            SumFetch1, SumFetch2, SumFetch3, SumFetch4,
x            SumFetch5

20 END DO

STOP
END

0

LVL 30

Expert Comment

Calculating the Shortest Flight Path - Math Forum - Ask Dr. Math should answer your question "apply this basic idea to excel but with using the coordinates themselves"...

0

LVL 30

Expert Comment

Here is another possible solution: Turn latitude/longitude into X,Y coordinates
0

LVL 43

Expert Comment

See if this file works.

The formulas have been inserted in column P for reference and have been acquired from

http://www.movable-type.co.uk/scripts/latlong.html

Saqib
DistLatLong.xls
0

LVL 81

Expert Comment

References:

Title: Calculate distance, bearing and more between Latitude/Longitude points

Title: Intersection point of two lines (2 dimensions)

Kevin
Q-26974315.xls
0

Author Comment

zorvek, this code you created is along the lines of what I am looking for.  My only problem is when I put these corners into google earth, the distance calculated by your file doesnt coincide with the google earth distance. In this example figure the distance from the "center" point is 1.04 km from the edge as calculated by google earth.  Your spreadsheet has a very different result.

Center      W119 30 26.80      N46 06 40.64      -119.50744      46.11129
Top Right Corner      W119 29 46.82      N46 06 58.75      -119.49634      46.11632
Top Left Corner      W119 31 00.03      N46 07 00.55      -119.51668      46.11682
Bottom Left Corner      W119 30 59.98      N46 06 07.78      -119.51666      46.10216
Bottom Right Corner      W119 29 46.13      N46 06 05.90      -119.49615      46.10164

12.jpg
0

LVL 81

Accepted Solution

Fixed. I was not considering the direction of the line and, for that specific angle, was returning the distance to the top line versus the bottom.

Kevin
Q-26974315.xls
0

Author Closing Comment

wow - high five.
0

## Featured Post

### Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.