[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1039
  • Last Modified:

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.

 Field Example
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
0
SullivanEnvironmental
Asked:
SullivanEnvironmental
  • 4
  • 2
  • 2
  • +1
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Can you give me a set of coorditantes?
0
 
SullivanEnvironmentalAuthor Commented:
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
 
SullivanEnvironmentalAuthor Commented:
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

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Paul SauvéCommented:
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
 
Paul SauvéCommented:
Here is another possible solution: Turn latitude/longitude into X,Y coordinates
0
 
Saqib Husain, SyedEngineerCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
SullivanEnvironmentalAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
SullivanEnvironmentalAuthor Commented:
wow - high five.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now