1

When SOG is 0 Stops gets 1. When the next SOG is 0 then Stops gets 2 and like this consecutively.

Now I want to group all the rows where SOG != 0. The trick is that I should have the following fields:

MinTime: minimum value of BS and TS, ie., date and time of boat arrival to port.

MaxTime: maximum value of BS and TS

Duration: difference between MinTime and MaxTime

AveLat and AveLong: Average Latitude and Longitude. Probably the most challenging. See formula at the bottom of the message.

This is what I have:

      MMSI       BS       TS      LAT     LONG SOG SIZE_A Stops
 247117300 6.4.2014 15:56:07 57.71432 11.96005   0     46     1
 247117300 6.4.2014 16:05:07 57.71433 11.96005   0     46     1
 247117300 6.4.2014 16:11:07 57.71432 11.96005   0     46     1
 247117300 6.4.2014 16:20:06 57.71433 11.96005   0     46     1
 247117300 6.4.2014 16:29:06 57.71433 11.96003   0     46     1
 247117300 6.4.2014 16:29:27 57.71433 11.96003   4     46     0
 247117300 6.4.2014 16:34:28 57.71433 11.96003   4     46     0
 247117300 6.4.2014 16:37:29 57.71433 11.96003   4     46     0
 247117300 6.4.2014 17:14:40 57.71433 11.96003   4     46     0
 247117300 6.4.2014 17:18:30 57.71432 11.96003   4     46     0
 247117300 6.4.2014 17:22:50 57.71433 11.96002   4     46     0
 247117300 6.4.2014 17:27:01 57.71432 11.96002   4     46     0
 247117300 6.4.2014 17:29:09 57.71435 11.96003   0     46     2
 247117300 6.4.2014 17:33:50 57.71435 11.96003   0     46     2
 247117300 6.4.2014 17:39:49 57.71437 11.96003   0     46     2
 247117300 6.4.2014 17:42:51 57.71435 11.96003   0     46     2
 247117300 6.4.2014 17:51:49 57.71433 11.96003   0     46     2
 247117300 6.4.2014 17:52:37 57.71432 11.96002   0     46     2
 247117300 6.4.2014 17:58:26 57.71212 11.95697   3     46     0
 247117300 6.4.2014 18:00:26 57.71047 11.95567   4     46     0

This is the desired result (AveLAT and AveLONG are fake):

     MMSI       BS       TS      LAT     LONG SOG SIZE_A Stops           MinTime           MaxTime Duration_min   AveLAT   AveLON
247117300 6.4.2014 15:56:07 57.71432 11.96005   0     46     1 6.4.2014 15:56:07 6.4.2014 16:29:06           34 57.71432 11.96005
247117300 6.4.2014 17:29:09 57.71435 11.96003   0     46     2 6.4.2014 17:29:09 6.4.2014 17:52:37           23 57.71433 11.96003

I have done the previous operations in R but I could also use ArcMap or whatever program solves it.

Formula to get Average LAT and LONG (from this website)

  1. LatToRadians: value*Pi/180
  2. LongToRadians: value*Pi/180
  3. X_cartesian: COS(LatToRadians) * COS(LongToRadians)
  4. Y_Cartesian: COS(LatToRadians) * SIN(LongToRadians)
  5. Z_Cartesian: SIN(LatToRadians)
  6. AveX: SUM (X_Cartesian)/ all occurrences of X_Cartesian
  7. AveY: SUM (Y_Cartesian)/ all occurrences of Y_Cartesian
  8. AveZ: SUM (Z_Cartesian)/ all occurrences of Z_Cartesian
  9. LAT: Inverse tangent (HYP, AveZ)
  10. LONG: Inverse tangent (AveX, AveY)
  11. HYP: Square root(AveX * AveX + AveY * AveY) 12: LATMean: LAT * 180 / PI
  12. LONGMean: LONG * 180 / PI
  • 1
    The base function to apply a function to a conditional factor is ?tapply – Jeffrey Evans Nov 24 '14 at 19:42
1

First, you should add some fields :

1) sogtest (select by attribute SOG!=0, and use field calculator to fill the selected values with 1)

2) X, Y and Z (use the field calculator to compute your "cartesian" coordinates)

Then you can use "dissolve"(or summarize table if you don"t need to merge the geometries) with sogtest as a case field and the required statistics of the other fields.

3) finally, you use the field calculator again to revert to Lat and Long coordinates

  • Thanks for the hint! Someone posted an example in Stackoverflow to do it with R. I will try with that first – Manuel Frias Nov 25 '14 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.