SullivanEnvironmental
asked on
Distances based on GPS coordinates
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
Thanks in advance
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
Thanks in advance
Saqib Husain
Can you give me a set of coorditantes?
ASKER
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
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
ASKER
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
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"...
Here is another possible solution: Turn latitude/longitude into X,Y coordinates
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
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
References:
Title: Calculate distance, bearing and more between Latitude/Longitude points
Link: http://www.movable-type.co.uk/scripts/latlong.html
Title: Intersection point of two lines (2 dimensions)
Link: http://paulbourke.net/geometry/lineline2d/
Kevin
Q-26974315.xls
Title: Calculate distance, bearing and more between Latitude/Longitude points
Link: http://www.movable-type.co.uk/scripts/latlong.html
Title: Intersection point of two lines (2 dimensions)
Link: http://paulbourke.net/geometry/lineline2d/
Kevin
Q-26974315.xls
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
wow - high five.